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
v-piga-msft's avatar
v-piga-msft
Icon for Resident Rockstar rankResident Rockstar
8 years ago

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'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.