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...
Need Help
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.