SLA equation

Question

Dear Power BI team,see the table below, I need the equation to calculate the column SLA.&nbsp;DateTimeReceivedDateTime.LocalNow()Working hours SLA3/1/19 7:00 AM3/1/19 11:00 AMfrom 9:00 till 17:002h2/28/19 7:00 AM3/1/19 11:00 AMfrom 9:00 till 17:0010h2/28/19 4:00 PM3/1/19 11:00 AMfrom 9:00 till 17:003h2/27/19 4:00 PM3/1/19 11:00 AMfrom 9:00 till 17:0011h&nbsp;Thank you

v-piga-msft · Answer

Hi Marcel_Licko&nbsp;,
&nbsp;
I still have a little confused about your desired output.
&nbsp;
If it is convenient, could you explain your logic to calculate the SLA so that we could help further on it?
&nbsp;
Best Regards,
Cherry

marcel_licko · Answer

Hi&nbsp;Cherry,the calculation of SLA has to be just in the time from 9:00 a.m. till 5:00 p.m.&nbsp;So when you receive a email before 9:00 a.m. like 7:00 a.m. the count will start from 9:00 a.m. and be calculated only till 5:00 p.m. and again start on next day from 9:00 a.m. to add the hours till the email is removed from the box.&nbsp;Like 1day&nbsp; =&nbsp; 8 Hours ( from 9 a.m. till 5:00 p.m )&nbsp;RgdsMarcel

v-piga-msft · Answer

Hi&nbsp;Marcel_Licko&nbsp;,
&nbsp;
Sorry, I still confused about your SLA output based on your sample data.
&nbsp;

DateTimeReceived
DateTime.LocalNow()
Working hours
SLA

3/1/19 7:00 AM
3/1/19 11:00 AM
from 9:00 till 17:00
2h

2/28/19 7:00 AM
3/1/19 11:00 AM
from 9:00 till 17:00
10h

2/28/19 4:00 PM
3/1/19 11:00 AM
from 9:00 till 17:00
3h

2/27/19 4:00 PM
3/1/19 11:00 AM
from 9:00 till 17:00
11h

&nbsp;
Why is the SLA of the second record is 10 h? Could you explain the logic in details.
&nbsp;
Best&nbsp; Regards,
Cherry

anonymous · Answer

Hi Guys,&nbsp;I would also like to know a solution to this problem.As Marcel indicated in his table&nbsp;DateTimeReceivedDateTime.LocalNow()Working hoursSLA3/1/19 7:00 AM3/1/19 11:00 AMfrom 9:00 till 17:002h2/28/19 7:00 AM3/1/19 11:00 AMfrom 9:00 till 17:0010h2/28/19 4:00 PM3/1/19 11:00 AMfrom 9:00 till 17:003h2/27/19 4:00 PM3/1/19 11:00 AMfrom 9:00 till 17:0011h&nbsp;The SLA for row 1:- Age is 4h difference between TimeNow and TimeReceived- Working time starts at 9AM- Therefore SLA is 4h - (11-9) = 2hThe SLA for row 2:- Age is 28h- Working time ends at 5PM and starts at 9AM- Therefore SLA for February 28 is 8h (the whole day maximum) + (11-9) = 10h&nbsp;And the same logic continues. Here is the version when email comes in after 5PMDateTimeReceivedDateTime.LocalNow()Working hoursSLA2/28/19 6:00 PM3/1/19 11:00 AMfrom 9:00 till 17:002h&nbsp;Still only 2 hours, because only working time is taken under consideration in this SLA calculation

Forum Discussion

SLA equation

4 Replies

Related content

Trend Line Equation

Charting SLA Monthly Percentages

SLA calculation - filter statuses->assign SLA Y/N -> calculate SLA%

Architecting SLA-based monitoring & alerting in Fabric

SLA calculation

DateTimeReceived	DateTime.LocalNow()	Working hours	SLA
3/1/19 7:00 AM	3/1/19 11:00 AM	from 9:00 till 17:00	2h
2/28/19 7:00 AM	3/1/19 11:00 AM	from 9:00 till 17:00	10h
2/28/19 4:00 PM	3/1/19 11:00 AM	from 9:00 till 17:00	3h
2/27/19 4:00 PM	3/1/19 11:00 AM	from 9:00 till 17:00	11h