Calculate SLA(Service Level Agreement) Due and Performance

[Anonymous]

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:

  

[Anonymous]

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?

[v-stephen-msft]

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]

Hi @v-stephen-msft 

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.