calculate time difference in an event list

[Martin_MG]

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.

Assistance would be appreciated.

[Cmcmahan]

Sure. I just answered a similar question earlier today: https://community.powerbi.com/t5/Desktop/Datediff-between-rows-in-subgroups-of-rows/m-p/759117#M3658...

In your case, since you don't want to sum the durations of each day and you DO care about the activity type (entry vs exit), you'll need to update the measure to not use SUMX or to use eventID field as the first parameter in the SUMX function. 

How would you want this displayed?  A table with badge ID, the Entry time, the associated Exit time, and duration between the two?  

[Martin_MG]

My thought was to calculate the time between entry and exit. For example: badge id 67966 would have total time on site of 5hrs 18 min 54 sec. I could then use existing visualizations to filter the day/time and badge id as needed. There are going to be errors / inconsistencies to flag for review or disregard out of the data set. If the inconsistencies were remarked somehow these could be filtered into a visualization for a quality check. I am open for different points of view to solve this. This would be used to calculate total hours of contractors on site that have read their access badge for entry and exit. The badge id and event local time would be filtered through visualizations. The result would be a table of (sum) hours that the user of the report could use a slider to filter (some) local time.

[Cmcmahan · Accepted solution]

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) 
)

[Martin_MG]

This is a great starting point for me. Thank you.