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 in Others by Kithuzzz
• 20,660 points
359 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 by narikkadan
• 37,660 points

Related Questions In Others

+1 vote
1 answer
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 in Others by narikkadan
• 37,660 points
87 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 in Others by narikkadan
• 37,660 points
111 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 in Others by gaurav
• 22,040 points
33 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,890 points
4,132 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 1,459 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
543 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
17,561 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 in Others by narikkadan
• 37,660 points
44 views
0 votes
1 answer

Differentiate between Tokyo and Seoul Time zone

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

answered Feb 11 in Others by narikkadan
• 37,660 points
99 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