Calculation of Calendar time vs total time for incidents

[Anonymous]

I am working with customer incident data but my calculations are adding up all the time for every incident.

My issue is that some incidents overlap each other and share the same time frame.

Example:

Incident #1 is on 1/1/21 from 6 AM to 9 AM

Incident # 2 is on 1/1/21 from 7:30 to 10 AM

The incident time total is 3 hours for #1 and 2.5 hours for #2 for a total of 5.5 hours.

However, I'm looking to view downtime for all incidents without double counting the time, so really, calendar-wise, the downtime was actually 4 hours instead of 5.5.

Anyone working with data like this and how have you handled/displayed it?

Thank you.

[Icey · Accepted solution]

Hi @Anonymous ,

Please check if this could meet your requirements:

Measure = 
VAR CurrentIncident_ =
    MAX ( 'Table'[Incident] )
VAR LastEnd_ =
    CALCULATE (
        MAX ( 'Table'[End DateTime] ),
        FILTER ( ALL ( 'Table' ), 'Table'[Incident] = CurrentIncident_ - 1 )
    )
VAR ThisStart_ =
    MAX ( 'Table'[Start DateTime] )
VAR ThisEnd_ =
    MAX ( 'Table'[End DateTime] )
RETURN
    IF (
        ThisStart_ < LastEnd_,
        DIVIDE ( DATEDIFF ( LastEnd_, ThisEnd_, MINUTE ), 60 ),
        DIVIDE ( DATEDIFF ( ThisStart_, ThisEnd_, MINUTE ), 60 )
    )

Measure 2 = SUMX('Table',[Measure])

In addition, some blogs for reference if needed:

Find overlapping time periods using #PowerQuery - DataChant;

Find overlapping time periods using #PowerQuery - Part 2 - DataChant

Best Regards,

Icey

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

[mahoneypat]

In my experience, the best way to do this is to make a disconnected table of time intervals (e.g., every 15 or 30 minutes and then iterate over that and count/add any interval that has 1 or more incidents (i.e., incidents that start before the end of the interval and end after the start of the interval).  If needed, you can then iterate over every day and for every day iterate over all the time intervals to get total incident down time.

Pat