Forum Discussion

Marcel_Licko's avatar
Marcel_Licko
Regular Visitor
7 years ago

SLA Hours email calculation using [DateTimeReceived]

Hello Power BI team,

I need to calculate the SLA of an email from the time we receive the email but we should only calculate the time from 9:00 till 17:00 so outside of this time no age should be added. So far I have onlt the calculation = DateTime.LocalNow() - [DateTimeReceived] but this inludes the time outside of our working hours.

 

I need something like this:

=DateTime.LocalNow( from 9:00 - till 17:00 ) - [DateTimeReceived]

 

 

 

 

[DateTimeReceived]

Need Help
Show and Tell
Tips and Tricks

5 Replies

  • v-frfei-msft's avatar
    v-frfei-msft
    Community Support
    7 years ago

    Hi Marcel_Licko ,

     

    We can add the steps as below in Power query. Please refer to the M code.

     

    let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjIwtNQ3MAQiBUMjKwMDIFKK1YGJG+kbGikYWGARNzBRMDSDiccCAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [DateTimeReceived = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"DateTimeReceived", type datetime}}),
    #"Added Custom" = Table.AddColumn(#"Changed Type", "Custom", each DateTime.Time(DateTime.LocalNow())),
    #"Added Custom1" = Table.AddColumn(#"Added Custom", "Custom.1", each DateTime.Time(#datetime(2019,01,01,09,0,0))),
    #"Added Custom2" = Table.AddColumn(#"Added Custom1", "17:00", each DateTime.Time(#datetime(2019,01,01,17,0,0))),
    #"Renamed Columns" = Table.RenameColumns(#"Added Custom2",{{"Custom", "local"}, {"Custom.1", "9:00"}}),
    #"Added Custom3" = Table.AddColumn(#"Renamed Columns", "Custom", each if[local]>=[#"9:00"] and [local]<=[#"17:00"] then DateTime.LocalNow() - [DateTimeReceived] else 0)
in
    #"Added Custom3"

     

    Also please find the pbix as attached.

     

    Regards,

    Frank

    • v-frfei-msft's avatar
      v-frfei-msft
      Community Support
      7 years ago

      Hi Marcel_Licko ,

       

      Does that make sense? If so, kindly mark my answer as the solution to close the case please. Thanks in advance.

       

      Regards,
      Frank

      • Marcel_Licko's avatar
        Marcel_Licko
        Regular Visitor
        7 years ago

        Hi Frank,

        It did not work,

         

        It seems this STEP is not applied on the data source = Online Services / Microsoft Exchange Online:

        #"Added Custom" = Table.AddColumn(#"Changed Type", "Custom", each DateTime.Time(DateTime.LocalNow())),

         

        I removed it and the result was the same, please make your test on your email exchange.

        My version of MS Power BI Desktop - Version: 2.61.5192.641 64-bit (August 2018)

         

        the mail received at 2/21/2019 8:00:54 AM should have SLA 4:43:35 till 1:44:30 PM & not 0.05:43:35

        the mail received at 2/21/2019 7:46:29 AM should have SLA 4:43:35 till 1:44:30 PM & not 0.05:58:00

         

        let
    Source = Exchange.Contents("any email address"),
    Mail1 = Source{[Name="Mail"]}[Data],
    #"Changed Type" = Table.TransformColumnTypes(Mail1,{{"DateTimeReceived", type datetime}, {"Folder Path", type duration}}),
    #"Added Custom" = Table.AddColumn(#"Changed Type", "Custom", each DateTime.Time(DateTime.LocalNow())),
    #"Added Custom1" = Table.AddColumn(#"Added Custom", "Custom.1", each DateTime.Time(#datetime(2019,01,01,09,0,0))),
    #"Added Custom2" = Table.AddColumn(#"Added Custom1", "17:00", each DateTime.Time(#datetime(2019,01,01,17,0,0))),
    #"Renamed Columns" = Table.RenameColumns(#"Added Custom2",{{"Custom", "local"}, {"Custom.1", "9:00"}}),
    #"Added Custom3" = Table.AddColumn(#"Renamed Columns", "Custom", each if[local]>=[#"9:00"] and [local]<=[#"17:00"] then DateTime.LocalNow() - [DateTimeReceived] else 0),
    #"Reordered Columns" = Table.ReorderColumns(#"Added Custom3",{"Folder Path", "Subject", "Sender", "DisplayTo", "DisplayCc", "ToRecipients", "CcRecipients", "BccRecipients", "DateTimeSent", "Importance", "Categories", "IsRead", "HasAttachments", "Attachments", "Preview", "Attributes", "Body", "Id", "DateTimeReceived", "local", "9:00", "17:00", "Custom"})
in
    #"Reordered Columns"

         

         

         

         

         

    • Marcel_Licko's avatar
      Marcel_Licko
      Regular Visitor
      7 years ago

      Hello,

       

      I have udated your table with todays times and dates and it is not working.

       

      2/21/2019 8:00:00 AM result = 0.07:09:46.0459200  it should calculate after 9:00 that means 0.06:09:46.0459200  till 3:09:46 PM

      2/21/2019 7:00:00 AM result = 0.08:09:46.0459200  it should calculate after 9:00 that means 0.06:09:46.0459200  till 3:09:46 PM

       

  • Anonymous's avatar
    Anonymous
    Not applicable
    7 years ago

    I am also very intrested in Solution to this problem.

    Let me try to add my 2 cents. The problems with SLA calculation based on email received time are:

     

    1) How to tell Power BI to stop counting Age of the received emails after 5PM? And resume from 9AM the next day?

    Example to this: I receive an email at 3PM on May 2nd. I open up my Power BI report the next day on 10AM. I see that the SLA for that email received yesterday is 19 hours. In fact, it should be only 3.

    Of course I can setup a new column which will automatically substract -16 hours from the age of my SLA. But this only works if the email was received the previous day. And before 5 PM. 

     

    2) If the email is received after 5PM, there is no fixed time I can substract...

    Example: I receive an email at 7PM on May 2nd. I open up my Power BI report the next day on 10 AM. I see that SLA count is 15 hours. In fact it should be 1 hour.

     

    3) If the email is received the same day, that is doable

     

    However I cant think of any good formula to solve all 3 problems at the same time. The way I see it:

    - First we need to distinguish between emails received the same day and the day before

    - Then we need to add 2 rules for the previous day emails = Before 5 PM substract 16 hours from SLA. After 5 PM substract 15 hours if email received after 6PM (including), 14 hours after 7PM (including) etc.

    - Then we need to sort out the same day emails in a separate rule

     

    Any ideas?