Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and a 50 percent discount on exams.
Get startedEarn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
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
I am trying to determine the number of orders (Order Ref) that were processed within our Service Level Agreement (SLA), here are the rules:
Please reach out if you need any clarification.
Looking forward to seeing your suggestions.
Sal
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 👍.
Proud to be a Super User!
Hi, apologies, I just realised that. The file should now be accessible.
I will read through your response in the meantime. Thanks
try the solutions first. then let me know if it doesn't work. I will try to look into it
Proud to be a Super User!
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
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.
Proud to be a Super User!
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
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.
Proud to be a Super User!