Forum Discussion
Adding 3 Workdays to a Date
Please Help, I have worked on this for some time and can't seem to find a solution online. I am needing to add 3 Workdays to an Application date. The application date can be any day of the year, b...
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
Anonymous
I create a sample to demonstrate the solution. Basically I created flag and rank column before getting the add 3 days column, check the pbix if needed.
isworkingday = IF([Working Day]="Workingday",TRUE())
rank = RANKX(FILTER('Table',[isworkingday]=TRUE()),[Date],,AS
Add3days = CALCULATE(MAX([Date]),FILTER('Table',[rank]=EARLIER([rank])+3))
Paul Zheng _ Community Support Team
If this post helps, please Accept it as the solution to help the other members find it more quickly.
What I did is bascially the same thing. You will notice on first date, you added 3 days and it brings back 1/7/21. That's incorrect. It should be 1/6/21.