Forum Discussion
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-gbNYMGJz0l0KU...
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.
Thanks.
Mohan V
Hi Anonymous,
Based on my test, it could work on my side:
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.
Regards,
Daniel He
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.
Thanks.
Mohan V