Calculate Elapsed Time

[UncleLewis]

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

[UncleLewis · Accepted solution]

Thanks,

See sample data and Calulcated Elapsed Time in Col D

Thanks

w

SN	Step	DateTime	Elapsed Time Calculated
10	1	2/2/2022 6:01:10	NULL
10	2	2/2/2022 8:12:37	2.190833333
20	1	2/2/2022 7:13:39	NULL
20	2	2/2/2022 9:05:11	1.858888889
20	3	2/3/2022 6:16:21	21.18611111
30	1	2/2/2022 10:03:11	NULL
30	2	2/2/2022 13:10:14	3.1175
30	3	2/3/2022 7:27:37	18.28972222
30	4	2/3/2022 15:29:49	8.036666667
30	5	2/4/2022 9:39:12	18.15638889

[serpiva64]

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 !

[sevenhills]

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

[sevenhills]

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.

[serpiva64]

Hi,

can you post some sample data?