Forum Discussion
Martin_MG
7 years agoFrequent Visitor
calculate time difference in an event list
The image is that of an event history list. I want to calculate the time difference between reader function [entry] and [exit]. Then I would use filters for badge id and date to summarize. Assistanc...
- 7 years ago
So it's super unwieldy, and I figure there must be a better soltion out there, but here's the measure I came up with that displays results in HH:MM:ss format. If you put it in a table with EMP.BADGE.ID, you get a total duration any exit entries within the sliced timeframe. Hopefully this gives you a workable starting point.
Time Onsite = VAR Duration = CALCULATE( SUMX( ADDCOLUMNS( VALUES(Table2[Event Local Time]), "Duration", DATEDIFF( CALCULATE( MAX(Table2[Event Local Time]), FILTER(ALLEXCEPT(Table2, Table2[EMP.BADGE.ID]), Table2[Event Local Time]<EARLIER(Table2[Event Local Time]) && Table2[Readers.Reader Function]="Entry") ), Table2[Event Local Time], SECOND) ), [Duration] ), Table2[Readers.Reader Function]="Exit") RETURN IF( //Convert from seconds to HH:MM:SS Duration<>BLANK(), INT(Duration / 3600) & ":" & RIGHT("0" & INT((Duration-INT(Duration/3600)*3600) / 60), 2) & ":" & RIGHT("0" & MOD(Duration, 60),2) )
Cmcmahan
Resident Rockstar
7 years agoSo it's super unwieldy, and I figure there must be a better soltion out there, but here's the measure I came up with that displays results in HH:MM:ss format. If you put it in a table with EMP.BADGE.ID, you get a total duration any exit entries within the sliced timeframe. Hopefully this gives you a workable starting point.
Time Onsite =
VAR Duration = CALCULATE(
SUMX(
ADDCOLUMNS(
VALUES(Table2[Event Local Time]),
"Duration", DATEDIFF(
CALCULATE(
MAX(Table2[Event Local Time]),
FILTER(ALLEXCEPT(Table2, Table2[EMP.BADGE.ID]), Table2[Event Local Time]<EARLIER(Table2[Event Local Time]) && Table2[Readers.Reader Function]="Entry")
),
Table2[Event Local Time], SECOND)
),
[Duration]
),
Table2[Readers.Reader Function]="Exit")
RETURN
IF( //Convert from seconds to HH:MM:SS
Duration<>BLANK(),
INT(Duration / 3600) & ":" &
RIGHT("0" & INT((Duration-INT(Duration/3600)*3600) / 60), 2) & ":" &
RIGHT("0" & MOD(Duration, 60),2)
)Martin_MG
7 years agoFrequent Visitor
This is a great starting point for me. Thank you.