Forum Discussion
Adding 3 Workdays to a Date
Since counting in PQ starts at 0, you need to subtract 1 from the working days to get correct value from the list of working days.
Here is the function version, updated with that.
(startdate as date, workingdays as number) =>
let
Source = List.Select(
List.Dates( // make a list of dates
startdate,
workingdays + 1 + Number.RoundUp(workingdays / 5, 0) * 2, // go out far enough to include due date in working days
#duration(1, 0, 0, 0)
),
each List.Contains({1..5}, Date.DayOfWeek(_)) // keep just dates that are Monday through Friday
){workingdays - 1} // get the needed value from the list you've created
in
SourcePat
The approach should work here too. Basically, in DAX or M, you filter out a list/table of the dates beyond your start date (go out 5-7 days based on worst-case scendario of weekend/holiday that might occur), filter those to just working days, and choose the 3rd value in your case.
Pat
The issue this causes, is when the start date in on a non working day... See below image.
For example, if the start date is 1/2/21(Saturday), 3 workdays from that date is 1/6/21(Wednesday)... Not the 7th that was returned.
I built out exactly how you did.
Since counting in PQ starts at 0, you need to subtract 1 from the working days to get correct value from the list of working days.
Here is the function version, updated with that.
(startdate as date, workingdays as number) =>
let
Source = List.Select(
List.Dates( // make a list of dates
startdate,
workingdays + 1 + Number.RoundUp(workingdays / 5, 0) * 2, // go out far enough to include due date in working days
#duration(1, 0, 0, 0)
),
each List.Contains({1..5}, Date.DayOfWeek(_)) // keep just dates that are Monday through Friday
){workingdays - 1} // get the needed value from the list you've created
in
SourcePat