Power Query Multiple conditions - SLAs

[Sal29]

Hi,

I am faced with a Power Query problem, and I hope someone can support me, here is a link to a sample file of orders and Public Holiday tables from our database connected to a Power Bi Report Power Query- SLA Calculation- Sal

UKBankHolidays 

I am trying to determine the number of orders (Order Ref) that were processed within our Service Level Agreement (SLA), here are the rules:

1. Business Hours
• 7:00-19:00
• Monday to Friday
• No work on Public Holiday (Bank Holiday)
2. If an order is created (Order Created Date) within business hours, then it needs to be packed on the same day. (SLA-Pass)
3. If an order is created (Order Created Date) outside of business hours, then it needs to be packed (Order Packed Date) on the next available business hours. (SLA-Pass).
4. If an order fails to be packed within the above conditions (2 and 3), then SLA has not been achieved (SLA-Fail).

Please reach out if you need any clarification.

Looking forward to seeing your suggestions.

Sal

[rubayatyasmin]

HI, @Sal29 

File is not accessable. Can you see to that? 

First try this solution. Create a function that determines Next business day. Example code. 

let
NextBusinessDay = (inputDate as date) as date =>
let
nextDay = Date.AddDays(inputDate, 1),
nextWeekday = if Date.DayOfWeek(nextDay, Day.Monday) > 4 then Date.AddDays(nextDay, 7 - Date.DayOfWeek(nextDay, Day.Monday)) else nextDay,
Holidays = Table.Column(#"Public Holidays", "Date"), //replace with your table of public holidays
result = if List.Contains(Holidays, nextWeekday) then @NextBusinessDay(nextWeekday) else nextWeekday
in
result
in
NextBusinessDay

then in your order table calculate whether each order was processed within the SLA.

example code:

let
Source = #"Orders", //replace with your table of orders
AddedCustom = Table.AddColumn(Source, "SLA Status", each let
orderCreatedDate = [Order Created Date], //replace with your column names
orderPackedDate = [Order Packed Date],
orderCreatedTime = Time.Hour(orderCreatedDate),
orderCreatedIsBusinessHours = orderCreatedTime >= 7 and orderCreatedTime < 19 and not Date.IsInCurrentWeekend(orderCreatedDate),
deadline = if orderCreatedIsBusinessHours then orderCreatedDate else @NextBusinessDay(orderCreatedDate)
in
if orderPackedDate <= deadline then "SLA-Pass" else "SLA-Fail")
in
AddedCustom

Here is the documentation on how to create function. https://learn.microsoft.com/en-us/power-query/custom-function

if my assistance helped you in any way, hit 👍. 

[Sal29]

Hi, apologies, I just realised that. The file should now be accessible. 

I will read through your response in the meantime. Thanks

[rubayatyasmin]

try the solutions first. then let me know if it doesn't work. I will try to look into it

[Sal29]

unfortunately I'm stuck with the function, here is what I adopted from your code. I have also added the table of all holidays on the main post.

let
NextBusinessDay = (inputDate as date) as date =>
let
nextDay = Date.AddDays(inputDate,1),
nextWeekday = if Date.DayOfWeek (nextDay,Day.Monday) > 4 then Date.AddDays(nextDay,7 - Date.DayOfWeek(nextDay,Day.Monday)) else nextDay,
BankHolidays = BankHolidayCalendar (#"Bank holiday","Date"),
result = if List.Contains (BankHolidays, nextWeekday) then @NextBusinessDay(nextWeekday) else nextWeekday
in
result
in
NextBusinessDay

[rubayatyasmin]

HI, @Sal29 

I tried in a different and easy way. here is the demo file. File will be automatically deleted after download. I just copied your data. You might need to adjust the query steps with your original data. 

Here is a summery of what I did. 

1. Created name of the day column for both order date and packed date.  "Add Column" > "Date" > "Day" > "Name of Day." Do this for both Order Created Date and Order Packed Date.

2. Extracted hour from the add column> Date> time>hour. for both order and packed date. 

3. Finally, a custom column is created, resulting value is SLA-fail or Pass. 

[Sal29]

Hi, thanks for your efforts so far, much appreciated.

However, I checked the file you shared with me and the summary of what you did (above)

Unfortunately, the logic in the custom column in your file seems to "SLA-Fail" all orders.

Also I don't seem to see any consideration for Public Holidays.

Thanks

[rubayatyasmin]

Hey, you do need to have a separate for public holidays. Then the solution will work. The solution is done assuming you have a separate table for holidays. 

need to adjust with the solution I gave you. The solution is an example how you can accomplish your goal.