Forum Discussion

sarawilson's avatar
sarawilson
Frequent Visitor
6 years ago

Capture Outstanding IT Ticket Count over Time Time

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!

 

Need Help

5 Replies

  • amitchandak's avatar
    amitchandak
    Super User
    6 years ago

    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's avatar
      sarawilson
      Frequent Visitor
      6 years ago

      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. 

       

       

    • sarawilson's avatar
      sarawilson
      Frequent Visitor
      6 years ago

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

       

      MonthCount
      Nov-19*297
      Oct-19236
      Sep-19221
      Aug-19179
      Jul-19214
      Jun-19308
      May-19275
      Apr-19262
      Mar-19285
      Feb-19268
      Jan-19254
      • Ashish_Mathur's avatar
        Ashish_Mathur
        Super User
        6 years ago

        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.