Forum Discussion
Calculating Time Difference between Start and End Timestamps excluding overlapping of multiple rows
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.
Please help with the Calculation.
Warm Regards
sushmitasur4 , First All will two datetime column
Start Datetime = [Start Date] + [Start Time]
End Datetime = [End Date] + [End Time]
Now will create a new end time
New end datetime =
var _min = minx(filter(Table, Table, [Start DateTime] >= Earlier([Start DateTime]) && [Start DateTime] <= Earlier([End DateTime]) && [Event] > earlier([Event]) ), [Start DateTime])
return
if(isblank(_min, [End Datime], _min)
Then you can use date diff of start datetime with the New end datetime
1 Reply
sushmitasur4 , First All will two datetime column
Start Datetime = [Start Date] + [Start Time]
End Datetime = [End Date] + [End Time]
Now will create a new end time
New end datetime =
var _min = minx(filter(Table, Table, [Start DateTime] >= Earlier([Start DateTime]) && [Start DateTime] <= Earlier([End DateTime]) && [Event] > earlier([Event]) ), [Start DateTime])
return
if(isblank(_min, [End Datime], _min)
Then you can use date diff of start datetime with the New end datetime