Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.

Reply
Lazurens
Frequent Visitor

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

 

 

Lazurens_0-1699276314249.png

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

I appreciate any help to make it work. 

1 ACCEPTED SOLUTION
amitchandak
Super User
Super User

@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

View solution in original post

1 REPLY 1
amitchandak
Super User
Super User

@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

Helpful resources

Announcements
LearnSurvey

Fabric certifications survey

Certification feedback opportunity for the community.

PBI_APRIL_CAROUSEL1

Power BI Monthly Update - April 2024

Check out the April 2024 Power BI update to learn about new features.

April Fabric Community Update

Fabric Community Update - April 2024

Find out what's new and trending in the Fabric Community.