I want to calculate SLO (Service Level Objective) using m-query. SLO column is mainly time calculation for time required to solve a ticket after opening it.
We have 3 types of priorities based on which time should be calculated
P1 (Urgent) which requires 60 minutes
P2 (High) requires 120 minutes
and P3 (Medium) will require 8hours (480 minutes)
And business working hours are 07:00ET - 19:00ET (Monday - Friday)
So, for example: if I open ticket on Monday 07:00 ET with priority P3, SLO calculated should be 8 hours (480 minutes) on same day i.e Monday 7:00ET - 15:00ET.
But If I open the ticket on Monday 12:00ET with priority P3 then 7 hours from Monday(19:00ET) but I will need 1 hour more to be added in Tuesday, so SLO will be
7 hours of Monday(12:00ET - 19:00ET ) + 5hours (19:00ET - 00:00 i.e 12 am night)+ 7 hours till Tuesday 7:00ET + 1hour of Tuesday = 20 hours (1200 min)
Also if I open ticket on Friday at 15:00ET then I will close it on Monday 11:00 ET as 4 business hrs of Friday and 4 hrs of Monday.
So SLO = 4hours (Mon 15:00 -19:00) + 5hours (Friday 19:00 - 00:00)+ 48hours(sat & sun as they are not business days) +7hours till Monday morning +4hours (Monday) = 68hours (4080 min)
Please assist me with figuring out how to calculate the time in the SLO column.
Your assistance is deeply appreciated and helpful for me.
Below is the query on which I am working now :
Table.AddColumn(#"Changed Type", "SLO", each
if [PriorityName] = "P1 - Urgent" then 60
else if [PriorityName] = "P2 - High" then 120
else if [PriorityName] = "P3 - Medium" then
let
nextBusinessDay =
if Date.DayOfWeek([DateOpened]) = 6 then
Date.From(Date.AddDays([DateOpened], 2))
else if Date.DayOfWeek([DateOpened]) = 7 then
Date.From(Date.AddDays([DateOpened], 1))
else
Date.From([DateOpened]),
businessStartTime = DateTime.From(nextBusinessDay) + #duration(0, 7, 0, 0),
businessEndTime = DateTime.From(nextBusinessDay) + #duration(0, 19, 0, 0),
hoursInBusinessDay = Duration.From(businessEndTime - businessStartTime) / #duration(0, 1, 0, 0),
remainingHours = hoursInBusinessDay - (Time.Hour([DateOpened]) - 7)
in
if remainingHours <= 0 then 0 else remainingHours * 60
else 0
)
