Calculate the next business day excluding holidays and weekends using Power Query

[Anonymous]

The result in place of the question mark should be May 4th.

May 1st is a public holiday

I have a holiday dim

Column date is the start date

The SLA column is the number of days you have for treatment

And the "EXPECT DATE" column should be the sum of the "DATE" plus the "SLA" column excluding "holidays" and "weekends"

Someone can help me? tks

[CNENFRNL · Accepted solution]

Hi, @Anonymous , here's a solution to your question, you may tweak the code accordingly,

let
    #"Holidays" = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjIwMtA1MNU1MFSK1UHimijFxgIA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Date = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Holidays,{{"Date", type date}}),
    holidays = #"Changed Type"[Date],
    
    Start = #date(2020,4,30),
    gap = 1,
    plan = Date.AddDays(Start, gap),
    DayList = List.Generate(
        () => plan,
        each List.Contains(holidays, _) or Date.DayOfWeek(_, Day.Monday) >= 5,
        each Date.AddDays(_, 1)
    ),
    NextWorkDay = Date.AddDays(List.Last(DayList), 1)??plan
in
    NextWorkDay

btw, I mark May 4 a holiday on purpose for testing functionality of the M code.