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. Assistanc...
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) 
)
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) 
)
  • Martin_MG's avatar
    Martin_MG
    Frequent Visitor
    7 years ago
    This is a great starting point for me. Thank you.