Forum Discussion

Marcel_Licko's avatar
Marcel_Licko
Regular Visitor
7 years ago

SLA equation

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...
Need Help
Show and Tell
Tips and Tricks
v-piga-msft's avatar
v-piga-msft
Resident Rockstar
7 years ago

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's avatar
Marcel_Licko
Regular Visitor
7 years ago

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's avatar
    v-piga-msft
    Resident Rockstar
    7 years ago

    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's avatar
      Anonymous
      Not applicable
      7 years ago

      Hi Guys,

       

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

      As Marcel indicated in his table

       

      DateTimeReceivedDateTime.LocalNow()Working hoursSLA
      3/1/19 7:00 AM3/1/19 11:00 AMfrom 9:00 till 17:002h
      2/28/19 7:00 AM3/1/19 11:00 AMfrom 9:00 till 17:0010h
      2/28/19 4:00 PM3/1/19 11:00 AMfrom 9:00 till 17:003h
      2/27/19 4:00 PM3/1/19 11:00 AMfrom 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

      DateTimeReceivedDateTime.LocalNow()Working hoursSLA
      2/28/19 6:00 PM3/1/19 11:00 AMfrom 9:00 till 17:002h

       

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