Forum Discussion

Lazurens's avatar
Lazurens
Frequent Visitor
2 years ago
Solved

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

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. 

Need Help
Tips and Tricks
  • amitchandak's avatar
    amitchandak
    2 years ago

    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

1 Reply

  • amitchandak's avatar
    amitchandak
    Icon for Super User rankSuper User
    2 years ago

    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