Calculate Hours with taking into account Business Days and Business Hours Power Query

[Anonymous]

Hi All,

I hope that someone can help me with this case.

I would like to calculate if client emails [Email_Date] were registrered [Call_Creation_Date] within 4 hours and take the business days and business hours into account. I have previously reported this in Excel with the following formula:

(NETWORKDAYS(F2;G2)-1)*(AX$2-AW$2)  +IF(NETWORKDAYS(G2;G2);MEDIAN(MOD(G2;1);AX$2;AW$2);AX$2)  - MEDIAN(NETWORKDAYS(F2;F2)*MOD(F2;1);AX$2;AW$2)

Where: F2 = [Email_Date], G2 = [Call_Creation_Date], AW2 = Start Business Day = 8:00, AX2 = End Business Day = 17:30

I tried to produce this formula in the Power Query for Custom Column, and broke it down because of the 'if' statement.

([Days_Difference]*([End BD]-[Start BD)) - List.Median(([Business Day]*(Number.Mod([Email_Date],1))),([End BD]),([Start BD]))

Where: [Days_Difference] = the amount of days between [Email_Date] and [Call_Creation_Date], End BD = manually added column = 17:30,  Start BD = manually added column = 8:00, [Business Day] = dummy column with 0 if Saturday or Sunday and 1 if else.

Unfortunately, the formula gives errors for every observation. I hope that someone can help me with this issue by putting me on the right track, providing an alternative or the solution as a whole 🙂

Many thanks!

Juss

[mahoneypat]

Here is one approach using List functions in the query editor to calculate total working minutes between two datetimes (excluded Sat and Sun, and hours outside 8-5).  To see how it works, just create a blank query, go to Advanced Editor, and replace the text there with the M code below.  I broke it up into multiple Add Column steps to make it easier to follow, but it could all be done in a single custom column.  I'm not sure how performant this will be on a large dataset.  Please report back with how long it takes on how many rows, if you end up using it.

let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMtQ31DcyMDJQMLQyMFAI8FXSQQgZQ4VidUDqjFAFQeqMIUKWICFHmDpjTHVmUCsMrYwhCmMB", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Email Time" = _t, #"Call Time" = _t]),
#"Changed Type" = Table.TransformColumnTypes(Source,{{"Email Time", type datetime}, {"Call Time", type datetime}}),
CalcTotalMinutes = Table.AddColumn(#"Changed Type", "TotalMinutes", each Duration.TotalMinutes([Call Time]-[Email Time]), Int64.Type),
MakeListOfMinutes = Table.AddColumn(CalcTotalMinutes, "MinutesList", each List.DateTimes([Email Time], [TotalMinutes], #duration(0,0,1,0))),
RemoveNonWorkingMinutes = Table.AddColumn(MakeListOfMinutes, "WorkingMinutes", each List.Select(List.Select([MinutesList], each Time.Hour(_)>=8 and Time.Hour(_)<=16), each Date.DayOfWeek(_, Day.Monday) < 5)),
CountWorkingMinutes = Table.AddColumn(RemoveNonWorkingMinutes, "WorkingMinutesCount", each List.Count([WorkingMinutes])),
MeetsGoalYesNo = Table.AddColumn(CountWorkingMinutes, "MeetGoal", each if [WorkingMinutesCount] <= 240 then "Yes" else "No", type text)
in
MeetsGoalYesNo

If this works for you, please mark it as the solution.  Kudos are appreciated too.  Please let me know if not.

Regards,

Pat

[dax]

Hi @Anonymous , 

I don't know your detailed error information, so if possible, could you please inform me more detailed information(such as your expected output and your sample data )? Then I will help you more correctly.

Please do mask sensitive data before uploading.

Thanks for your understanding and support.
Best Regards,
Zoe Zhi

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

[lbendlin]

I hope this is only for a "Single Country Single Timezone Weekend is Sat/Sun" scenario?

How are you computing [Days_Difference] ?

Power Query uses plain IF ... THEN ... ELSE ... syntax without ENDIF or similar, so only singular operations are allowed for the three dots.  Nevertheless it should be possible to transfer your Excel IF logic to that format.  Where did you get stuck in implementing the IF part?

[Anonymous]

Hi Ibendlin,

Thank you for responding.

I calculate [Days_Difference] as "[Call_Creation_Date] - [Email_Date]". 

I don't understand what you mean with "Single Country Single Timezone Weekend is Sat/Sun"-scenario. 

The struggle was with continuing the formula with the "if" statement. I wanted to make sure that I have the part right before the "if" statement, so I can add the "if"-part in another custom column. Afterwards I could add the latter with the first formula.

Thanks,

Juss

[lbendlin]

I support teams that operate worldwide, across all time zones, with wildly varying ideas of what "business hours"  mean, with weekends that are not Saturday/Sunday, city specific holidays etc etc.  Calculating business hour intervals in such an environment is, uhm, interesting.