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

[sushmitasur4]

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.

Kindly help with the Calculation

Warm Regards.

[v-jianpeng-msft · Accepted solution]

Hi,  @sushmitasur4 

First of all,
Here is a reference, I did some processing to your data, you can use [Date]+[Time] to get the [Date/Time] Column:

Add an index column for identification:

Sort =
SWITCH (
    TRUE (),
    'Table'[Event Num] IN { 1, 2 }, "1",
    'Table'[Event Num] = 3, "2",
    'Table'[Event Num] IN { 4, 5, 6 }, "3",
    'Table'[Event Num] IN { 7, 8 }, "4"
)

Then create a calculate table:

Table 2 =
FILTER (
    CROSSJOIN (
        SELECTCOLUMNS ( 'Table', "Sort1", 'Table'[Sort], "Start1", 'Table'[Start] ),
        SELECTCOLUMNS ( 'Table', "Sort2", 'Table'[ Sort], "End2", 'Table'[End] )
    ),
    [Sort1] = [Sort2]
)

At last, add a table visual, The result is as follow:

 

How to Get Your Question Answered Quickly

If it does not help, please provide more details with your desired output and pbix file without privacy information (or some sample data) .

Best Regards

Jianpeng Li

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.