Capture Outstanding IT Ticket Count over Time Time

[sarawilson]

I have what I hope is an interesting problem for someone...

I'm tracking IT performance and am trying to capture the number of tickets open greater than 30 days over time. I'm struggling with the over time because our data doesn't have a process date. I have Open Date and Resolved Date on the incident. 

At first pass I was trying to capture any tickets that are currently open and > 30 days or anything that was resolved after more than 30 days of being opened: 

# > 30d = CALCULATE(count(Incident[0 ID]), filter(Incident, and(Incident[IsResolved] = blank(), Incident[0 Open Date] + 29) || Incident[ResolvedDate] > Incident[0 Open Date] + 29 ))

but I realize this is is grouping by the date a ticket was opened (below) rather than how the ticket stood at the end of any given month. So (incorrectly) each ticket will only show up in one row even if it's open over the course of several months.  I'm really not confident in DAX yet to know how to use functions like ALLEXCEPT, etc. but I feel like this should be possible! 🙂 

Thank you in advance for any help/guidance!

[v-shex-msft]

Hi @sarawilson ,

Can you please share some sample data for test?

How to Get Your Question Answered Quickly  

Regards,
Xiaoxin Sheng 

[sarawilson]

Sure! While I can't share the pbix file, here is the (simplified) underlying data.

Also, here are what totals should be (*Nov as of 11/27):

Month	Count
Nov-19*	297
Oct-19	236
Sep-19	221
Aug-19	179
Jul-19	214
Jun-19	308
May-19	275
Apr-19	262
Mar-19	285
Feb-19	268
Jan-19	254

[Ashish_Mathur]

Hi,

How did you arrive at the figure of 214 for July 2019?  With respect to the MS Excel file that you have shared, please ber very clear with what filters/conditions have you applied to arrive at that figure.

[amitchandak]

Try like this. Make sure you have a calendar/date table

Open Month = CALCULATE(count(Incident[0 ID]),filter(Incident,(Incident[0 Open Date]+29)<=MAXx('Date',ENDOFMONTH('Date'[Date Filer]))
&& ( isblank(Incident[ResolvedDate]) ||  Incident[ResolvedDate]>=Maxx('Date',startOFMONTH('Date'[Date Filer])))))

Appreciate your Kudos. In case, this is the solution you are looking for, mark it as the Solution. In case it does not help, please provide additional information and mark me with @
Thanks.

My Recent Blog - https://community.powerbi.com/t5/Community-Blog/Comparing-Data-Across-Date-Ranges/ba-p/823601

[sarawilson]

Hi @amitchandak and thank you!

Unfortunately this isn't producing the desired results because isblank(incident(resolveddate)) doesn't account for incidents that are closed over time (if a ticket was 30+d outstanding as of March but then is resolved in April, it will fall off the report; see below).

I should also point out that I do have a calendar/date table and there is a relationship between the Date and Incident Tables (Open Date). However, I tried creating a reference to the Incident table to test removing the relationship but that also didn't work.