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

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

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
July 2024 Power BI Update

Power BI Monthly Update - July 2024

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

July Newsletter

Fabric Community Update - July 2024

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