Calculate Ticket SLA

Question

Hi All,&nbsp;I’ve been requested to calculate the&nbsp; tickets SLA&nbsp; by&nbsp; adding all the open tickets and tickets closed in last 30 days. I have columns "Date closed" and&nbsp; "Ticket Status(inprogress, open, closed).&nbsp; what could be the DAX formula to achieve this?&nbsp;Thank you in advance for your help!!&nbsp;Regards,Pinky.&nbsp;

v-piga-msft · Answer

Hi&nbsp;Pinky0404,&nbsp;I'm a little confused about the mean of SLA?&nbsp;Could you provide me some data sample and your expected output,&nbsp;so that we can help further investigate on it?&nbsp;Best Regards,Cherry

pinky0404 · Answer

Hi,&nbsp;Thanks for responding. Here is the sample data.&nbsp;&nbsp;Average SLA :&nbsp; Average number of days for all Open tickets and tickets closed in last 30 days based on the date submitted and date closed column.&nbsp;&nbsp;Open Tickets are all tickets other than Closed ones.&nbsp;Thanks,Pinky.&nbsp;&nbsp;&nbsp;

chandeepchhabra · Answer

Pinky0404&nbsp;Hello,&nbsp;Quick question - How should the final result look like ? Also last 30 days from the (current) today's date ?&nbsp;&nbsp;

pinky0404 · Answer

Hi ,&nbsp;Thanks for your response. The output should be bascially a trend with date( i get this from the source file&nbsp; export date) on X- axis and&nbsp; the average&nbsp; no. of days for&nbsp; all open tickets&nbsp;plus&nbsp; no. of days for 30 days closed tickets.&nbsp;I used the below DAX formulas to get this trend which show&nbsp;&nbsp;an average no. of days for all open and closed tickets.&nbsp;&nbsp;&nbsp;SC = IFERROR(DATEDIFF(Footprint[Date Submitted],Footprint[Date Closed],DAY),Footprint[SO])&nbsp;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!!&nbsp;&nbsp;Thanks,Pinky.&nbsp;&nbsp;&nbsp;&nbsp;

pinky0404 · Answer

I feel like i got the soultion. But not sure if it is the right way to do.&nbsp;Created the below DAx:&nbsp;1)ALL Open = IF(Footprint[New status]&lt;&gt;"Closed",Footprint[SO])&nbsp;2)Closed in LAst 30 days = IF((Footprint[Date Closed]&gt; Footprint[RD]-30) &amp;&amp; (Footprint[Date Closed]&lt;=Footprint[RD]),"Closed 30 days","Null")&nbsp;3)Days - Closed(30 days) = IF(Footprint[Closed in LAst 30 days] = "Closed 30 days",Footprint[SC])&nbsp;4)SLA Final = IF(ISBLANK(Footprint[ALL Open]),Footprint[Days - Closed(30 days)],Footprint[ALL Open])&nbsp;Output:&nbsp;&nbsp;&nbsp;&nbsp;Please let me know if i can achieve this in a more better way.&nbsp;Thanks,Pinky&nbsp;

Forum Discussion

Calculate Ticket SLA

7 Replies

Related content

SLA calculation

SLA calculation - filter statuses->assign SLA Y/N -> calculate SLA%

SLA Calculation

Calculating outstanding tickets

SLA Calculation weight by country