Calculating Time Duration with Lag: How to Measure the Time Between Two Events with a One-Step Delay

[Lazurens]

Hello, 

I would like to calculate the duration in seconds between two events spent on the same equipment tool.

DurationInSeconds = 
VAR Current_DateTime = MAX(UTILITY_HISTORY[EV_TIME])
VAR T1 =
    CALCULATETABLE (
        'UTILITY_HISTORY',
        ALLEXCEPT ( 'UTILITY_HISTORY', 'UTILITY_HISTORY'[EQUIP_ID] )
    )
VAR T2 =
    FILTER ( T1, UTILITY_HISTORY[EV_TIME] < Current_DateTime )
VAR T3 =
    TOPN ( 1, T2, UTILITY_HISTORY[EV_TIME], DESC )
VAR NextDateTime =
    SELECTCOLUMNS ( T3, "@DateTime", UTILITY_HISTORY[EV_TIME] )
VAR Result =
    IF ( NOT ISBLANK ( NextDateTime ), Current_DateTime - NextDateTime )
RETURN
    Result

Can anyone please help me figure out how list durations starting from step-1 ?

I appreciate any help to make it work. 

[amitchandak · Accepted solution]

@Lazurens , Use offset function

New measure = Calculate(Max(Table[Duartion]), offset(-1, allselected(Table[Equip], Table[EV_time]), Orderby([EV_time],asc) )

Continue to explore Power BI Offset Compare Categories, Time Intelligence MOM, QOQ, and YOY: https://youtu.be/5YMlkDNGr0U