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.
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
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
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
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
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
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
112 | |
100 | |
80 | |
64 | |
57 |
User | Count |
---|---|
145 | |
111 | |
92 | |
84 | |
66 |