Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.

Reply
Anonymous
Not applicable

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) ScorecardTickets 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:

ShivS_7-1652689144235.png

 

ShivS_5-1652688613820.png  

ShivS_6-1652688633070.png

 

 

 

 

3 REPLIES 3
Anonymous
Not applicable

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?

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
Not applicable

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.

 

Helpful resources

Announcements
Microsoft Fabric Learn Together

Microsoft Fabric Learn Together

Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City

April Fabric Community Update

Fabric Community Update - April 2024

Find out what's new and trending in the Fabric Community.

March 2024 PBI Gallery Image

Power BI Monthly Update - March 2024

Check out the March 2024 Power BI update to learn about new features.