Forum Discussion

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

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's avatar
ChandeepChhabra
Icon for Impactful Individual rankImpactful 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
    Icon for Helper III rankHelper 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
      Icon for Helper III rankHelper 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
        Icon for Impactful Individual rankImpactful 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