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.
Hi All,
I’ve been requested to calculate the tickets SLA by adding all the open tickets and tickets closed in last 30 days. I have columns "Date closed" and "Ticket Status(inprogress, open, closed). what could be the DAX formula to achieve this?
Thank you in advance for your help!!
Regards,
Pinky.
Hi @Pinky0404,
I'm a little confused about the mean of SLA?
Could you provide me some data sample and your expected output, so that we can help further investigate on it?
Best Regards,
Cherry
Hi,
Thanks for responding. Here is the sample data.
Average SLA : Average number of days for all Open tickets and tickets closed in last 30 days based on the date submitted and date closed column.
Open Tickets are all tickets other than Closed ones.
Thanks,
Pinky.
@Pinky0404 Hello,
Quick question - How should the final result look like ? Also last 30 days from the (current) today's date ?
Hi ,
Thanks for your response. The output should be bascially a trend with date( i get this from the source file export date) on X- axis and the average no. of days for all open tickets plus no. of days for 30 days closed tickets.
I used the below DAX formulas to get this trend which show an average no. of days for all open and closed tickets.
SC = IFERROR(DATEDIFF(Footprint[Date Submitted],Footprint[Date Closed],DAY),Footprint[SO])
SO = Footprint[RD]- Footprint[Date Submitted]
RD = MAX(Footprint[Date])
SLA(closed) = IF(ISBLANK(Footprint[SC]),Footprint[SO],Footprint[SC])
Hope you are clear now!!
Thanks,
Pinky.
I feel like i got the soultion. But not sure if it is the right way to do.
Created the below DAx:
1)ALL Open = IF(Footprint[New status]<>"Closed",Footprint[SO])
2)Closed in LAst 30 days = IF((Footprint[Date Closed]> Footprint[RD]-30) && (Footprint[Date Closed]<=Footprint[RD]),"Closed 30 days","Null")
3)Days - Closed(30 days) = IF(Footprint[Closed in LAst 30 days] = "Closed 30 days",Footprint[SC])
4)SLA Final = IF(ISBLANK(Footprint[ALL Open]),Footprint[Days - Closed(30 days)],Footprint[ALL Open])
Output:
Please let me know if i can achieve this in a more better way.
Thanks,
Pinky
Hi @Pinky0404,
Looks like you have created a lot of calculated columns. Found a bracket is missing in one of your measures
2)Closed in LAst 30 days = IF((Footprint[Date Closed]> (Footprint[RD]-30)) && (Footprint[Date Closed]<=Footprint[RD]),"Closed 30 days","Null")
Can you share your pbix file, I'll be able to write a measure that does the same
Please share me your email id.
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 |
---|---|
109 | |
98 | |
77 | |
66 | |
54 |
User | Count |
---|---|
144 | |
104 | |
100 | |
86 | |
64 |