Forum Discussion

Martin_MG's avatar
Martin_MG
Frequent Visitor
7 years ago
Solved

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.

Assistance would be appreciated.

 

Need Help
  • Cmcmahan's avatar
    Cmcmahan
    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) 
)

4 Replies

  • Cmcmahan's avatar
    Cmcmahan
    Icon for Resident Rockstar rankResident Rockstar
    7 years ago

    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#M365856

     

    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's avatar
      Martin_MG
      Frequent Visitor
      7 years ago

      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's avatar
        Cmcmahan
        Icon for Resident Rockstar rankResident Rockstar
        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) 
)