Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

The Power BI DataViz World Championships are on! With four chances to enter, you could win a spot in the LIVE Grand Finale in Las Vegas. Show off your skills.

Reply
E_K_
Helper III
Helper III

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

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

 

E_K__0-1737538691264.png

 

 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])
                )
            )
            
            )
        )
    )

 

 

 

 

 

2 REPLIES 2
saud968
Super User
Super User

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!

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

 

 

Helpful resources

Announcements
Feb2025 Sticker Challenge

Join our Community Sticker Challenge 2025

If you love stickers, then you will definitely want to check out our Community Sticker Challenge!

Jan NL Carousel

Fabric Community Update - January 2025

Find out what's new and trending in the Fabric community.