Forum Discussion

UncleLewis's avatar
UncleLewis
Responsive Resident
4 years ago
Solved

Calculate Elapsed Time

Hi all   How can I calulcate elapsed time from previous step at each change in record id? Some record id's will have 2 steps, some will have 25 steps, some have n steps.   Thanks, -w
Need Help
  • UncleLewis's avatar
    UncleLewis
    4 years ago

    Thanks,

    See sample data and Calulcated Elapsed Time in Col D

     

    Thanks

    w

     

    SNStepDateTimeElapsed Time Calculated
    1012/2/2022 6:01:10NULL
    1022/2/2022 8:12:372.190833333
    2012/2/2022 7:13:39NULL
    2022/2/2022 9:05:111.858888889
    2032/3/2022 6:16:2121.18611111
    3012/2/2022 10:03:11NULL
    3022/2/2022 13:10:143.1175
    3032/3/2022 7:27:3718.28972222
    3042/3/2022 15:29:498.036666667
    3052/4/2022 9:39:1218.15638889

     

UncleLewis's avatar
UncleLewis
Responsive Resident
4 years ago

Thanks,

See sample data and Calulcated Elapsed Time in Col D

 

Thanks

w

 

SNStepDateTimeElapsed Time Calculated
1012/2/2022 6:01:10NULL
1022/2/2022 8:12:372.190833333
2012/2/2022 7:13:39NULL
2022/2/2022 9:05:111.858888889
2032/3/2022 6:16:2121.18611111
3012/2/2022 10:03:11NULL
3022/2/2022 13:10:143.1175
3032/3/2022 7:27:3718.28972222
3042/3/2022 15:29:498.036666667
3052/4/2022 9:39:1218.15638889

 

sevenhills's avatar
sevenhills
Super User
4 years ago
Elapsed Time = 

var _SN = SELECTEDVALUE('Table'[SN])
var _Step = SELECTEDVALUE('Table'[Step]) 
var _Dt1 = SELECTEDVALUE('Table'[DateTime])
var _Prev_Dt1 = CALCULATE( Max('Table'[DateTime]), filter(all('Table'), 'Table'[SN] = _SN && 'Table'[Step] < _Step))
var _calc1 = Datediff(_Prev_Dt1, _Dt1, SECOND ) / (60.00000 * 60.000000)

RETURN _calc1