Forum Discussion

sushmitasur4's avatar
sushmitasur4
Regular Visitor
2 years ago
Solved

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

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.

Need Help
Tutorial Requests
  • Anonymous's avatar
    Anonymous
    2 years ago

    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.

     

     

     

     

     

1 Reply

  • Anonymous's avatar
    Anonymous
    Not applicable
    2 years ago

    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.