Days open band and create % of total

[_bs_]

Hello, my data is around service desk tickets and I need to work out for all the open tickets (ResolvedDate IS BLANK) how many have been open by day bands and the % of total. E.g

Days Open	Count of tickets	%
<1 days	1	0.9%
1-5 days	37	34.3%
6-30 days	68	62%
31-60 days	2	1.9%

Total = 108

sample data:

I have the total as:

CALCULATE(COUNT('Cases'[CaseNumber]),'Cases'[ResolvedDate] = BLANK ())

= 108

Need help with the numerator for each band, so for Day Band 1-5 days, it needs to filter to rows where the Sum of NoOfDaysOpen between 1 and 5 and then count the number of rows. 

Hope that makes sense.

Thank you.

[v-tejrama · Accepted solution]

Hi @_bs_ ,
Thanks for reaching out to the Microsoft fabric community forum.

After some experimentation, I successfully identified the optimal approach:

First, I created a calculated column using DATEDIFF to determine the number of days each case has been open, comparing CreatedDate and ResolvedDate, or TODAY() if unresolved.

Next, I introduced another column to categorize these values into bands such as <1 day, 1–5 days, 6–30 days, and so on.
Then, I built a measure with COUNTROWS to quantify the open tickets within each band.
Finally, I developed a percentage measure using the DIVIDE function, dividing the count for each band by the total open tickets (leveraging CALCULATE + ALL to bypass filter context). This method delivers precise insights into ticket distribution.

Please find the attached PBIX and Screenshort file for your reference.

Best Regards,
Tejaswi.
Community Support

[Arul]

@_bs_ ,

I assume You have already created Days calculated column. To calculate % use this measure,

% = DIVIDE (
    CALCULATE ( COUNT ( 'Cases'[CaseNumber] ), 'Cases'[ResolvedDate] = BLANK () ),
    CALCULATE (
        COUNT ( 'Cases'[CaseNumber] ),
        'Cases'[ResolvedDate] = BLANK (),
        ALL ( 'Cases' )
    )
)

[_bs_]

May have found the answer:

CALCULATE(COUNTROWS('Cases'),'Cases'[NoOfDaysOpen] >1,'Cases'[NoOfDaysOpen] <6, 'Cases'[ResolvedDate] = BLANK())

Then to work out % use DIVIDE function.

Is this the most efficient way?