Average TIME Calculation differs between Excel and PowerBI

0 votes

Here is a sampling of the data that I am working with before I get into further detail. All of the users' ins and outs are contained in this swipecard data.

https://1drv.ms/x/s!AhiQ2f7YQHC-gbNYMGJz0l0KU70svg

By computing the disparities between each user's total hours spent and their total hours spent, MIN (first in) and LO (last out) (MAX)

For the Min InTime and MaxOutTime, I used the dax calculated column in the accompanying table.

MinInTIME = CALCULATE(
  MIN('BaseData'[Date-Time]),
      FILTER(ALLEXCEPT('BaseData','BaseData'[EMP_ID],'BaseData'[Begin_Date]),'BaseData'[Type]="IN"))

OutTime

MAXOutTIME = CALCULATE(
  MAX('BaseData'[Date-Time]),
      FILTER(ALLEXCEPT('BaseData','BaseData'[EMP_ID],'BaseData'[Begin_Date]),'BaseData'[Type]="OUT"))

Then I calculated the spent hours by using the below Dax.

Spent Hours = SWITCH(TRUE(),
'BaseData'[MAXOutTime]=BLANK() && 'BaseData'[MinInTIME]='BaseData'[MinInTIME],BLANK(),
'BaseData'[MinInTIME]=BLANK() &&  'BaseData'[MAXOutTime]= 'BaseData'[MAXOutTime],BLANK(),
'BaseData'[MAXOutTime] < 'BaseData'[MinInTIME],BLANK(),
'BaseData'[MinInTIME]='BaseData'[MinInTIME] &&  'BaseData'[MAXOutTime]= 'BaseData'[MAXOutTime],FORMAT( 'BaseData'[MAXOutTime]-'BaseData'[MinInTIME],"HH:MM"))

I got the right values. But when I tried to calculate the average using below dax, then the issue occurs.

AvgSpendHours = FORMAT(AVERAGE('BaseData'[Spent Hours]),"HH:MM")

Output:-

enter image description here

I exported it into excel and checked the values Then it gives me below the average value which is mismatching with power bi Dax average value

which is, in Dax it is 13:13 and in excel it is 11:45

enter image description here

Is it with my DAX or PowerBI or with Excel?

Sep 20, 2022 in Others by Kithuzzz
• 38,010 points
2,151 views

1 answer to this question.

0 votes

They are calculating different things. In Power BI, you are taking the average OUT minus IN time for each pair. In Excel, you are taking the average of the daily average OUT minus IN.


Consider a simplified example over just two days:

 IN        |  OUT        | Spend Hours
-----------|-------------|------------
9AM 1/1/18 |  5PM 1/1/18 | 8
8AM 1/1/18 |  2PM 1/1/18 | 6
9AM 1/2/18 | 10PM 1/2/18 | 13

On 1/1/18, there are two ins and outs with a 7-hour average for that day. When you average that 7-hour average with the 13-hour average from 1/2/18, your Excel average per day is 10 hours. However, if you average over each row, you get the (8+6+13)/3 = 9-hour Power BI average per in/out pair.

answered Sep 21, 2022 by narikkadan
• 63,180 points

Related Questions In Others

+1 vote
1 answer

Between cyber security and CCNA profession which one is best in terms of time to become an expert and salary payment

CCNA professional is more inclined towards the ...READ MORE

answered Dec 18, 2019 in Others by Pri
1,438 views
0 votes
1 answer

Excel Formula multiple Index Match and Average the result

If the conditions are separate and unrelated, ...READ MORE

answered Oct 31, 2022 in Others by narikkadan
• 63,180 points
2,223 views
0 votes
1 answer

Calculate time difference in hours between two dates and times

Simply subtract the two dates to get ...READ MORE

answered Nov 4, 2022 in Others by narikkadan
• 63,180 points
1,157 views
0 votes
1 answer

Calculate the number of days between a cell and today in excel?

Use the DATEDIF function when you want ...READ MORE

answered Nov 8, 2022 in Others by gaurav
• 23,220 points
338 views
0 votes
1 answer

Using Multiple filters in DAX

CALCULATE is defined as CALCULATE(<expression>,<filter1>,<filter2>…) This means that you can ...READ MORE

answered Dec 22, 2020 in Power BI by Gitika
• 65,930 points
7,120 views
+3 votes
2 answers

Combine tables in Power BI

You can also achieve this using a ...READ MORE

answered Oct 5, 2018 in Power BI by lina
• 8,220 points

edited Oct 11, 2018 by Kalgi 2,000 views
0 votes
1 answer

Power Bi Dax Table

You need to wrap the numbers in ...READ MORE

answered Oct 5, 2018 in Power BI by Kalgi
• 52,350 points
863 views
0 votes
2 answers

Power BI Dax Multiple IF AND Statements

HI I have Column Patient 12 13 14 15 18 IN Patient=12, 13 Out ...READ MORE

answered May 23, 2019 in Power BI by VNK
18,223 views
0 votes
1 answer

Determine if calculation between 2 date time values is < 72 Hours in excel

Actually, Excel is quite accommodating in this ...READ MORE

answered Nov 12, 2022 in Others by narikkadan
• 63,180 points
498 views
0 votes
1 answer

Differentiate between Tokyo and Seoul Time zone

if DST and Offset are the only ...READ MORE

answered Feb 11, 2022 in Others by narikkadan
• 63,180 points
284 views
webinar REGISTER FOR FREE WEBINAR X
REGISTER NOW
webinar_success Thank you for registering Join Edureka Meetup community for 100+ Free Webinars each month JOIN MEETUP GROUP