Calculating Time Difference between Start and End Timestamps excluding overlapping of multiple rows.

[sushmitasur4]

Explanation:

We need a calculated Column/Measure that can calculate the time difference excluding the overlapping time between two Timestamp Columns: Start Time and End Time.

Each Row represents an event in the table. The Output (Minutes) column [Time Format – Minutes] is our desired output and the total is the calculated sum of the Output we need in Minutes format.

Basically, when there is any overlapping of time, we consider the start time stamp of first overlapped event and the end timestamp of last overlapped event.

For Example: Event 7 starts at 13:40PM on 28th and ends at 16:00PM of the same date. The Output can be 140 Minutes Time difference, but we have Event 8 that starts at 15:00PM which has overlapped between with the timing /Duration of Event 7. It ends at 17:50PM. Hence, we will take start time of Event 7 and End time of Event 8 and calculate the time difference that is 250 Minutes. This calculation goes on throughout the table.

[lbendlin · Accepted solution]

This is an extremely complex premise. You are basically asking for a grouping of events into overlap groups, and then calculating their combined runtime inside each group.

To do that you would have to compare each event against all the other events and note the overlaps. Then you would have to do the same for each event and all combinations of two other events. then the same for three other events etc.

This will be prohibitively compute intensive for anything over 20 events

Just kidding.  All you need to do is compare the start and end times of the events in the current sort order 

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("hY9BDoAgDAS/YjibWEoL2K8QDj7AhP+f1JQS8eJ1Z9LdluK8Wx1uSBsC0rILwHFOkQehJ6trcfgrowRoKgcl3Il/2TxsMJuURCUkxO2coihoLn9cBnPTKAu2Oc6kf/OxvdlJSe7klu10trYxOU8gKHhHrMtqvQA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Event Num" = _t, Start = _t, End = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Event Num", Int64.Type}, {"Start", type datetime}, {"End", type datetime}}),
    #"Sorted Rows" = Table.Sort(#"Changed Type",{{"Start", Order.Ascending}, {"End", Order.Ascending}}),
    #"Added Custom" = Table.AddColumn(#"Sorted Rows", "Group", each List.Accumulate({1..[Event Num]-1},1,(state,current)=> 

if #"Sorted Rows"{current}[Start]<= #"Sorted Rows"{current-1}[End] then state else state+1),Int64.Type),
    #"Grouped Rows" = Table.Group(#"Added Custom", {"Group"}, {{"Duration", each List.Max([End])-List.Min([Start]), type duration},  {"Rows", each _, type table [Event Num=nullable number, Start=nullable datetime, End=nullable datetime, Group=number]}}),
    #"Expanded Rows" = Table.ExpandTableColumn(#"Grouped Rows", "Rows", {"Event Num", "Start", "End"}, {"Event Num", "Start", "End"})
in
    #"Expanded Rows"

How to use this code: Create a new Blank Query. Click on "Advanced Editor". Replace the code in the window with the code provided here. Click "Done". Once you examined the code, replace the Source step with your own source.

The result will be in durations, not in minutes.