Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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:
Hi @sarawilson ,
Can you please share some sample data for test?
How to Get Your Question Answered Quickly
Regards,
Xiaoxin Sheng
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 |
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.
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
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.
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
97 | |
96 | |
81 | |
74 | |
66 |
User | Count |
---|---|
126 | |
105 | |
103 | |
81 | |
72 |