Forum Discussion

Pinky0404's avatar
Pinky0404
Helper III
8 years ago

Calculate Ticket SLA

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, c...
Need Help
ChandeepChhabra's avatar
ChandeepChhabra
Impactful Individual
8 years ago

Pinky0404 Hello,

 

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

 

 

Pinky0404's avatar
Pinky0404
Helper III
8 years ago

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's avatar
    Pinky0404
    Helper III
    8 years ago

    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's avatar
      ChandeepChhabra
      Impactful Individual
      8 years ago

      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's avatar
        Pinky0404
        Helper III
        8 years ago

        Please share me your email id.