cancel
Showing results for
Did you mean:
Super User

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

1 ACCEPTED SOLUTION
Super User

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

5 REPLIES 5
Super User

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

Super User

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]}}),
#"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"}),
#"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 !

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

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.

Super User

Hi,

can you post some sample data?

Announcements

#### Power BI September 2023 Update

Take a look at the September 2023 Power BI update to learn more.

#### Learn Live: Event Series

Join Microsoft Reactor and learn from developers.

#### Exclusive opportunity for Women!

Join us for a free, hands-on Microsoft workshop led by women trainers for women where you will learn how to build a Dashboard in a Day!

#### Power Platform Conference-Power BI and Fabric Sessions

Join us Oct 1 - 6 in Las Vegas for the Microsoft Power Platform Conference.

Top Solution Authors
Top Kudoed Authors