cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
Anonymous
Not applicable

Need Urgent Help - Average TIME Calculation differs between Excel and PowerBI

Before i go into the details, here is the sample data that i am working on.

this is swipecard data and it contains all the in's and out's of users..

 

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

 

 

I am trying to calculate the spent hours of each users by calculating their differences between 

First In(MIN) and Last Out(MAX)

 

I have used below dax Calculated column for the Min InTime and MaxOutTime.

 

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 have calculated the spent hours by using 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")

 

And the out put is

 

Capture3.JPG 

 

And then i exported it into excel and checked the values then it is giving me below value

Capture.JPG

 

 

I really dont understand why this is happening.

 

Is it with my DAX or PowerBI or with Excel.

 

Please correct me.

 

Thanks,

Mohan V

1 ACCEPTED SOLUTION

Accepted Solutions
Highlighted
Anonymous
Not applicable

Re: Need Urgent Help - Average TIME Calculation differs between Excel and PowerBI

@v-danhe-msft thanks for the reply.

 

 

I tried the below dax and it worked for me.

 

New table Dax:-

 

NewTable =
 ADDCOLUMNS(
    SUMMARIZE(
        Table,
        Table[EMP_ID],
        Table[BEGIN_DATE],
        Table[Emp Name]   
    ),

    "InTime", CALCULATE(MIN(Table[Date-Time]),FILTER(ALLEXCEPT(Table,Table[EMP_ID],Table[BEGIN_DATE]),
        Table[Type]="IN")),

    "OutTime",
    CALCULATE(MAX(Table[Date-Time]),FILTER(ALLEXCEPT(Table,Table[EMP_ID],Table[BEGIN_DATE]),
        Table[Type]="OUT"))
               
)

Then the output of this gives the correct values.

Capture.JPG

 

 

Thanks.

Mohan V

View solution in original post

2 REPLIES 2
Highlighted
Microsoft
Microsoft

Re: Need Urgent Help - Average TIME Calculation differs between Excel and PowerBI

Hi @Anonymous,

Based on my test, it could work on my side:

L.PNG

To find the cause as soon as possible, I would suggest you share pbix file if possible. Also you can test with our sample report on your side to see if the same issue occurs.

https://www.dropbox.com/s/6kp87yq0a9frn6o/Need%20Urgent%20Help%20-%20Average%20TIME%20Calculation%20differs%20between%20Excel%20and%20%20PowerBI.pbix?dl=0

 

Regards,

Daniel He

Community Support Team _ Daniel He
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Highlighted
Anonymous
Not applicable

Re: Need Urgent Help - Average TIME Calculation differs between Excel and PowerBI

@v-danhe-msft thanks for the reply.

 

 

I tried the below dax and it worked for me.

 

New table Dax:-

 

NewTable =
 ADDCOLUMNS(
    SUMMARIZE(
        Table,
        Table[EMP_ID],
        Table[BEGIN_DATE],
        Table[Emp Name]   
    ),

    "InTime", CALCULATE(MIN(Table[Date-Time]),FILTER(ALLEXCEPT(Table,Table[EMP_ID],Table[BEGIN_DATE]),
        Table[Type]="IN")),

    "OutTime",
    CALCULATE(MAX(Table[Date-Time]),FILTER(ALLEXCEPT(Table,Table[EMP_ID],Table[BEGIN_DATE]),
        Table[Type]="OUT"))
               
)

Then the output of this gives the correct values.

Capture.JPG

 

 

Thanks.

Mohan V

View solution in original post

Helpful resources

Announcements
Community Conference

Power Platform Community Conference

Check out the on demand sessions that are available now!

Upcoming Events

Experience what’s next for Power BI

See the latest Power BI innovations, updates, and demos from the Microsoft Business Applications Launch Event.

secondImage

Power Platform 2020 release wave 2 plan

Features releasing from October 2020 through March 2021

Get Ready for Power BI Dev Camp

Get Ready for Power BI Dev Camp

Mark your calendars and join us for our next Power BI Dev Camp!.

Top Solution Authors
Top Kudoed Authors