topic Re: Adding SLA +1 day to a Delivery Date excluding Weekends & Public Holidays in DAX Commands and Tips

Adding SLA +1 day to a Delivery Date excluding Weekends & Public Holidays

Anonymous — Tue, 27 Dec 2022 03:24:37 GMT

Hi Sir/Mdm,

I need some help with the Power BI query of adding a SLA of a day to a date while excluding Weekends and Public holidays

Have been trying to find similar answers here but has been futile.

Example: 30 Dec 2022 (friday) + 1 day SLA + excluding New Year Holiday; to return 4 Jan (since that 2 Jan is the New Year Observed Holiday + 1 day SLA)

In excel i could easily use workday to exclude the weekends and holidays + a day of SLA

Any advice and help with the code is truly appreciated. Thank you

Re: Adding SLA +1 day to a Delivery Date excluding Weekends & Public Holidays

amitchandak — Tue, 27 Dec 2022 04:02:13 GMT

Anonymous , You need to create column with flag of weekend and holiday, and then follow an approch like

https://community.powerbi.com/t5/Community-Blog/Travelling-Across-Workdays-Decoding-Date-and-Calendar-4-5-Power/ba-p/1187766

Column code is given in the comments

Re: Adding SLA +1 day to a Delivery Date excluding Weekends & Public Holidays

Jihwan_Kim — Tue, 27 Dec 2022 04:41:29 GMT

Hi,

I am not sure if I understood your question correctly, but please check the below formula and the attached pbix file.

It is for creating a new column.

Expected result CC = VAR _calendar = ADDCOLUMNS ( CALENDAR ( DATE ( 2022, 1, 1 ), DATE ( 2023, 12, 31 ) ), "@Dayname", FORMAT ( [Date], "dddd" ) ) VAR _holidaycalendar = SUMMARIZE ( FILTER ( ADDCOLUMNS ( _calendar, "@holiday", [Date] IN DISTINCT ( Holiday[Holiday] ) ), [@holiday] = TRUE () ), [Date] ) VAR _actualstartdate = ADDCOLUMNS ( Data, "@actualstartdate", MINX ( FILTER ( _calendar, [Date] >= EARLIER ( Data[Delivery date] ) && NOT ( [Date] IN _holidaycalendar ) && [@Dayname] <> "Saturday" && [@Dayname] <> "Sunday" ), [Date] ) ) VAR _networkdays = FILTER ( ADDCOLUMNS ( GENERATE ( _actualstartdate, CALENDAR ( [@actualstartdate], [@actualstartdate] + 30 ) ), "@networkdays", NETWORKDAYS ( [@actualstartdate], [Date], 1, _holidaycalendar ) ), [@networkdays] = 3 ) RETURN MINX ( FILTER ( _networkdays, Data[Delivery date] = EARLIER ( Data[Delivery date] ) ), [Date] )

Re: Adding SLA +1 day to a Delivery Date excluding Weekends & Public Holidays

Anonymous — Tue, 27 Dec 2022 05:55:51 GMT

Thank you so much Mr Jihwan, this works!. appreciate so much for the advice!

Re: Adding SLA +1 day to a Delivery Date excluding Weekends & Public Holidays

FreemanZ — Tue, 27 Dec 2022 06:08:18 GMT

hi Anonymous

Try to

1) load the holidays as a table.

2) create a DateTable like this:

DateTable = ADDCOLUMNS( CALENDAR(DATE(2022,12,1), DATE(2023,2,1)), "IsHolidayWeekend", IF( [Date] IN ALL(Holiday[Date]) || WEEKDAY([Date],2) IN {6,7}, "Yes", "No" ) )

3) add a column in your delivery table like this:

ExpectedDeliveryDate2 = VAR _date = [DeliveyDate] RETURN MINX( FILTER( DateTable, DateTable[IsHolidayWeekend] = "No" &&DateTable[Date]>_date ), DateTable[Date] )+1

I tried and it worked like this:

Re: Adding SLA +1 day to a Delivery Date excluding Weekends & Public Holidays

Anonymous — Tue, 27 Dec 2022 06:42:42 GMT

Hi Mr Jihwan,
Quick question, if i have wish to have a separate table for the SLA, how should i go about doing that?
apology for the question if this is a straight forward ans.. i'm still new to BI.

Re: Adding SLA +1 day to a Delivery Date excluding Weekends & Public Holidays

Jihwan_Kim — Tue, 27 Dec 2022 07:24:24 GMT

Hi,

I added a new table into the file by using the below formula.

It is for creating a new table.

Please check if it suits your requirement.

Expected result Table = VAR _calendar = ADDCOLUMNS ( CALENDAR ( DATE ( 2022, 1, 1 ), DATE ( 2023, 12, 31 ) ), "@Dayname", FORMAT ( [Date], "dddd" ) ) VAR _holidaycalendar = SUMMARIZE ( FILTER ( ADDCOLUMNS ( _calendar, "@holiday", [Date] IN DISTINCT ( Holiday[Holiday] ) ), [@holiday] = TRUE () ), [Date] ) VAR _actualstartdate = ADDCOLUMNS ( Data, "@actualstartdate", MINX ( FILTER ( _calendar, [Date] >= EARLIER ( Data[Delivery date] ) && NOT ( [Date] IN _holidaycalendar ) && [@Dayname] <> "Saturday" && [@Dayname] <> "Sunday" ), [Date] ) ) VAR _networkdays = FILTER ( ADDCOLUMNS ( GENERATE ( _actualstartdate, CALENDAR ( [@actualstartdate], [@actualstartdate] + 30 ) ), "@networkdays", NETWORKDAYS ( [@actualstartdate], [Date], 1, _holidaycalendar ) ), [@networkdays] = 3 ) RETURN GROUPBY ( _networkdays, Data[Delivery date], Data[Day Name], "@expectedresultdate", MINX ( CURRENTGROUP (), [Date] ) )