SLA Hours email calculation using [DateTimeReceived]

[Marcel_Licko]

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]

[marian_scipa]

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?

[v-frfei-msft]

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

[Marcel_Licko]

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

[v-frfei-msft]

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]

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"