Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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
And then i exported it into excel and checked the values then it is giving me below value
I really dont understand why this is happening.
Is it with my DAX or PowerBI or with Excel.
Please correct me.
Thanks,
Mohan V
Solved! Go to Solution.
@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
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
110 | |
94 | |
80 | |
67 | |
59 |
User | Count |
---|---|
150 | |
119 | |
104 | |
87 | |
67 |