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 ad...
Need Help
Show and Tell
Tips and Tricks
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