cancel
Showing results for
Did you mean:

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

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``````

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

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

Announcements

#### Fabric certifications survey

Certification feedback opportunity for the community.

#### Power BI Monthly Update - April 2024

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

#### Fabric Community Update - April 2024

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

Top Solution Authors
Top Kudoed Authors