SLA equation

[Marcel_Licko]

Dear Power BI team,

see the table below, I need the equation to calculate the column SLA.

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

Thank you

[v-piga-msft]

Hi @Marcel_Licko ,

I still have a little confused about your desired output.

If it is convenient, could you explain your logic to calculate the SLA so that we could help further on it?

Best Regards,

Cherry

[Marcel_Licko]

Hi Cherry,

the calculation of SLA has to be just in the time from 9:00 a.m. till 5:00 p.m. 

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.

Like 1day  =  8 Hours ( from 9 a.m. till 5:00 p.m )

Rgds

Marcel

[v-piga-msft]

Hi @Marcel_Licko ,

Sorry, I still confused about your SLA output based on your sample data.

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

Why is the SLA of the second record is 10 h? Could you explain the logic in details.

Best  Regards,

Cherry

[Anonymous]

Hi Guys,

I would also like to know a solution to this problem.

As Marcel indicated in his table

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

The SLA for row 1:

- Age is 4h difference between TimeNow and TimeReceived

- Working time starts at 9AM

- Therefore SLA is 4h - (11-9) = 2h

The 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

And the same logic continues. Here is the version when email comes in after 5PM

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

Still only 2 hours, because only working time is taken under consideration in this SLA calculation