Calculate Ticket SLA

[Pinky0404]

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.

[v-piga-msft]

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

[Pinky0404]

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.

[ChandeepChhabra]

@Pinky0404 Hello,

Quick question - How should the final result look like ? Also last 30 days from the (current) today's date ?

[Pinky0404]

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.

[Pinky0404]

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

[ChandeepChhabra]

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

[Pinky0404]

Please share me your email id.