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

     

5 Replies

  • sevenhills's avatar
    sevenhills
    Super User
    4 years ago

    Please post some sample mockup data and output expected.

     

    It sounds like you want to know the difference between first (min) time for a record id and current row time difference (or) max time difference.

     

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