try this one Calculate Nth Business Day From Date in Power Query - BI Gorilla
Edit:
I tested this function specifically myself but it's copied directly from link above. Seems to do the job. You'd need to plug in your own holidays though.
let func =
( ReferenceDate as date, WorkdayOffset as number, optional Holidays as list) =>
let
/* Debug parameters
ReferenceDate = #date( 2021,1,1 ),
WorkdayOffset = 1,
Holidays = { #date( 2021,1,1 ) },
*/
// Returns empty list if Holidays are omitted
BufferedHolidays = List.Buffer( Holidays ?? {} ),
// Determines the positive or negative offset of Date.AddDays
AddDays = Number.Sign( WorkdayOffset ),
// Creates the List of Dates until it reaches the relevant Workday
ListOfDates =
List.Generate(
() => [
Date = Date.AddDays( ReferenceDate, AddDays ),
WD_Counter = 0,
IsWorkday = null
],
each if [WD_Counter] = Number.Abs( WorkdayOffset )
and [IsWorkday] = true then false else true,
each
let
NextDateIsWD = Date.DayOfWeek([Date], 1)
< 5 and not List.Contains( BufferedHolidays, [Date])
in
[
Date = Date.AddDays([Date], AddDays ),
WD_Counter = if NextDateIsWD then [WD_Counter] + 1
else [WD_Counter],
IsWorkday = NextDateIsWD
],
each [Date]
),
// Returns the relevant Workday, which is always last in the list
RelevantWorkday = List.Last( ListOfDates ),
// When 0 is used as Workdayoffset, the function returns the referencedate
Result = if WorkdayOffset = 0 then ReferenceDate else RelevantWorkday
in
Result,
documentation = [
Documentation.Name = " WORKDAY ",
Documentation.Description = " Function returns the date that lies an specfied number of business days from the reference date. ",
Documentation.LongDescription = " Function returns the date that lies an specfied number of business days from the reference date. The function takes into account weekends and can optionally respect a list of holiday dates. ",
Documentation.Category = " Nth Business Day ",
Documentation.Source = " https://gorilla.bi – https://gorilla.bi/power-query/nth-business-day-from-date/ ",
Documentation.Version = " 1.0 ",
Documentation.Author = " Rick de Groot",
Documentation.Examples = {[Description = " ",
Code = " let
ReferenceDate = #date( 2022, 9, 15 ),
WorkdayOffset = 2,
Holidays = { #date( 2022, 9, 19 ) },
Result = WORKDAY( ReferenceDate, WorkDayOffset, Holidays )
in
Result ",
Result = " #date(2022, 9, 20)
"]}]
in
Value.ReplaceType(func, Value.ReplaceMetadata(Value.Type(func), documentation))
