Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
4 years ago

Calculate SLA(Service Level Agreement) Due and Performance

Can someone please help. I checked the other posts but could not figure out my approach.

I am trying to get the below 3 visuals ( and hence would be able to calculate the others):

1) Scorecard: Tickets with Response SLA Dues Today. These are the tickets that are due to be responded today.

If the status is NEW the SLA clock is still ticking. I have the CreatedTime, CreatedDate & ResponseSLATarget(hrs) to make this calculation.

To make it complicated, I also want to incorporate weekends and holidays(I have IsWorkingDay column which identifies if a day is woring or not.

2) Stacked Bar Chart: Response SLA performance today. This gives a bar chart of Response SLA Met vs Not Met for today as per SLA Targets.

3) Line Chart: Response SLA Met% Trend over 6 months. A Trend line that shows percentage of Response SLA Met over months.

Below is the link to the excel(has the field attributes, star schema and tables) and pbix file:

https://drive.google.com/drive/folders/15Rsm3LyzawHwjCuHQCrxARq1UpKqfIp1?usp=sharing 

 

I have 3 tables fo these calculations:

1) Date Table

2) Holiday Table

3) Fact Table

The schema for the tables are as below:

 

  

 

 

 

 

Need Help

3 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    4 years ago

    I had split my Date/time columns to Date & Time so that I could link the date to the Date Table. Or is it better to just duplicate the Date/Time column and use them for calculations?

    • Anonymous's avatar
      Anonymous
      Not applicable
      4 years ago

      Hi Anonymous ,

       

      Creating relationships between Date/time columns and Date columns can work.

      What is the logic of Response SLA Met %? How is it calculated?

       

       

      Best Regards,

      Stephen Tao

       

      If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

      • Anonymous's avatar
        Anonymous
        Not applicable
        4 years ago

        Hi Anonymous 

        Response SLA Met % = (Tickets that are responded within target time/ Total number of ticket)*100

         

        Our customers raise tickets and we need to respond to them within a given time. The time(target) could be 30 mins, 2/4/8 hours. It is met when status changes from NEW to WorkInProgess and within the target.

        For example, if a ticket was raised on 19-May-22 14:00 and has a response of 2 hours, its Due Date would be 19-May-22 16:00. If the status changes from New to WorkInProgress within this time the Response is Met. If not it is Not Met.

         

        Hope that helps.