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
  • serpiva64's avatar
    serpiva64
    Solution Sage
    4 years ago

    Hi,

    you can get something like this in power query:

    This are the steps you need:

    let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("ZZBBDsMgDAS/UnGOqNcOYPyGqLeeovz/G8VQlVQs8sUa79qcZwCFLaAVP9sj5kc2gvX2630c4dq+EN8hNbBJ8VZEJRVXZ3kxLAYxqXdDXgyrUTL4HKIm7aqTFa+5IbJxz0CEZrg6K0s4yEiG8S9dlvS2YAOxe0oESprcX3IxLuNsaGSthZsmu99ZJONqu9+tkSR3lQknH5jnS21fOoyRsozzrw8=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [SN = _t, Step = _t, DateTime = _t, #"Elapsed Time Calculated" = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"SN", Int64.Type}, {"Step", Int64.Type}, {"Elapsed Time Calculated", type text}, {"DateTime", type datetime}}),
    #"Grouped Rows" = Table.Group(#"Changed Type", {"SN"}, {{"Count", each _, type table [SN=nullable number, Step=nullable number, DateTime=nullable datetime, Elapsed Time Calculated=nullable text]}}),
    #"Added Custom" = Table.AddColumn(#"Grouped Rows", "Custom", each Table.AddIndexColumn([Count],"Index0",0)),
    #"Added Custom1" = Table.AddColumn(#"Added Custom", "Custom.1", each Table.AddIndexColumn([Custom],"Index1",1)),
    #"Removed Other Columns" = Table.SelectColumns(#"Added Custom1",{"Custom.1"}),
    #"Expanded Custom.1" = Table.ExpandTableColumn(#"Removed Other Columns", "Custom.1", {"SN", "Step", "DateTime", "Elapsed Time Calculated", "Index0", "Index1"}, {"SN", "Step", "DateTime", "Elapsed Time Calculated", "Index0", "Index1"}),
    #"Added Index" = Table.AddIndexColumn(#"Expanded Custom.1", "Index", 1, 1, Int64.Type),
    #"Merged Queries" = Table.NestedJoin(#"Added Index", {"SN", "Index1"}, #"Added Index", {"SN", "Index0"}, "Expanded Custom.1", JoinKind.LeftOuter),
    #"Expanded Expanded Custom.1" = Table.ExpandTableColumn(#"Merged Queries", "Expanded Custom.1", {"DateTime"}, {"DateTime.1"}),
    #"Sorted Rows" = Table.Sort(#"Expanded Expanded Custom.1",{{"Index", Order.Ascending}}),
    #"Added Conditional Column" = Table.AddColumn(#"Sorted Rows", "Elapsed Time", each if [DateTime.1] <> null then [DateTime.1]-[DateTime] else 0),
    #"Changed Type1" = Table.TransformColumnTypes(#"Added Conditional Column",{{"Elapsed Time", type duration}, {"DateTime", type datetime}}),
    #"Removed Columns" = Table.RemoveColumns(#"Changed Type1",{"Elapsed Time Calculated", "Index0", "Index1", "Index", "DateTime.1"}),
    #"Grouped Rows1" = Table.Group(#"Removed Columns", {"SN"}, {{"TotElapsedTime", each List.Sum([Elapsed Time]), type nullable duration}, {"AllRows", each _, type table [SN=number, Step=number, DateTime=nullable datetime, Elapsed Time=nullable duration]}}),
    #"Expanded AllRows" = Table.ExpandTableColumn(#"Grouped Rows1", "AllRows", {"Step", "DateTime", "Elapsed Time"}, {"Step", "DateTime", "Elapsed Time"})
    in
    #"Expanded AllRows"

     

    If this post is useful to help you to solve your issue consider giving the post a thumbs up 

     and accepting it as a solution !