Cumulative count of open incidents per hour, with a count of 1 where an incident spans multiple hour

[E_K_]

Hi! I'm working on a report to deliver a cumulative count of open incidents per hour, with a count of 1 where an incident spans multiple hour.

I have my incident data in 'Incident Data' with one unique row per Incident ID. They each have a [Start Time] (dd/mm/yy hh:mm:ss) and End Time. I have extracted the Start Hour and End hour from each timestamp as well to bucket the start and end hours. I've also added a column where each value is 1 to provide the counts (because I'm lazy to add even more clauses to a complex measure.

I have a calculated table which is just a series generation of integers from 0 -23 to represent hours.

I am working on a visual like below and do not want to generate a date time series table as that was the old formulation and was a very inefficient report.

I need to use a measure rather than a calculated table as users need to be able to filter by different fields in the Incident Data dataset.

Below is the measure I am using, and a snaposhot of the table I have created.

Am posting as I am not 100% convinced that my measure is correct. I don't like using only MAX in the expression

 CALCULATE(
    SUM('Incident Data'[Index]),
    FILTER(
        'Incident Data',
        (
            -- Case 1: Check Start Time and Resolved Time
            (
                
                'Incident Data'[Start Hour] >= MAX('TimeTable'[Hour]) &&
                'Incident Data'[End Hour] < MAX('TimeTable'[Hour])  + TIME(1, 0, 0)
            )
            ||
            -- Case 2: Check Start Time and Resolved Time for ongoing incidents (Resolved Time is blank)
            (
                
                'Incident Data'[Start Hour] <= MAX('TimeTable'[Hour])  &&
                (
                    'Incident Data'[End Hour] >= MAX('TimeTable'[Hour])  ||
                    ISBLANK('Incident Data'[End Hour])
                )
            )
            ||
            -- Case 3: Check Start Time and Resolved Time for incidents that span multiple hours
            (
               
                'Incident Data'[Start Hour] >= MAX('TimeTable'[Hour])  &&
                'Incident Data'[Start Hour] < MAX('TimeTable'[Hour])  + TIME(1, 0, 0) &&
                (
                    'Incident Data'[End Hour] >= MAX('TimeTable'[Hour])  + TIME(1, 0, 0) ||
                    ISBLANK('Incident Data'[End Hour])
                )
            )
            ||
            -- Case 4: Check for incidents that span multiple hours
            (
                
                'Incident Data'[Start Hour] >= MAX('TimeTable'[Hour])  &&
                (
                    ('Incident Data'[End Hour] >= MAX('TimeTable'[Hour])  &&
                    'Incident Data'[End Hour] < MAX('TimeTable'[Hour])  + TIME(1, 0, 0)) ||
                    ISBLANK('Incident Data'[End Hour])
                )
            )
            
            )
        )
    )

[saud968]

Here's a refined version of your measure:

CumulativeOpenIncidents =
CALCULATE(
SUM('Incident Data'[Index]),
FILTER(
'Incident Data',
(
-- Case 1: Incident starts and ends within the same hour
(
'Incident Data'[Start Hour] <= MAX('TimeTable'[Hour]) &&
'Incident Data'[End Hour] >= MAX('TimeTable'[Hour])
)
||
-- Case 2: Incident starts before the current hour and is still ongoing
(
'Incident Data'[Start Hour] <= MAX('TimeTable'[Hour]) &&
ISBLANK('Incident Data'[End Hour])
)
||
-- Case 3: Incident spans multiple hours
(
'Incident Data'[Start Hour] <= MAX('TimeTable'[Hour]) &&
'Incident Data'[End Hour] >= MAX('TimeTable'[Hour])
)
)
)
)
This measure simplifies the logic by focusing on three main cases:

Incidents that start and end within the same hour.
Incidents that start before the current hour and are still ongoing (end time is blank).
Incidents that span multiple hours.
By using <= and >= comparisons, it ensures that incidents are counted correctly for each hour they span.

Best Regards
Saud Ansari
If this post helps, please Accept it as a Solution to help other members find it. I appreciate your Kudos!

[E_K_]

Thank you for responding however this is not calculating correct values. The counts are about 3 times lower than what they should be.