cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Marcel_Licko Frequent Visitor
Frequent Visitor

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]

 

 

 

 

image.png

[DateTimeReceived]

4 REPLIES 4
Community Support Team
Community Support Team

Re: SLA Hours email calculation using [DateTimeReceived]

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"

Capture.PNG

 

Also please find the pbix as attached.

 

Regards,

Frank

Community Support Team _ Frank
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Community Support Team
Community Support Team

Re: SLA Hours email calculation using [DateTimeReceived]

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

Community Support Team _ Frank
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Marcel_Licko Frequent Visitor
Frequent Visitor

Re: SLA Hours email calculation using [DateTimeReceived]

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"

 

 

 

power Bi1.jpg

 

 

Marcel_Licko Frequent Visitor
Frequent Visitor

Re: SLA Hours email calculation using [DateTimeReceived]

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

 

add1.jpg