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

[Anonymous]

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

[FreemanZ]

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:

[Jihwan_Kim · Accepted solution]

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]
    )

[Anonymous]

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.

[Jihwan_Kim]

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] )
    )

[Anonymous]

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

[amitchandak]

@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