Adding 3 Workdays to a Date

[TSP_PowerBi]

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, but the workdays can not inlcude weekends or holidays.

So if the ApplicationDate is 8/18/2021, the date I need returned would be 8/23/2021.

If the ApplicationDate is 8/21/2021, the date I need returned would be 8/25/2021.

I am getting close, I just can't seem to get it exact 100%. 

Working Day = 
SWITCH(
    TRUE(),
    ISBLANK('Calendar'[IsHoliday])
    && 'Calendar'[Day of Week] <> 0
    && 'Calendar'[Day of Week] <> 6 , "Working Day",
    ISBLANK('Calendar'[IsHoliday])
    && 'Calendar'[Day of Week] = 0
    || 'Calendar'[Day of Week] = 6 , "Weekend",
    "Holidays")

IsWorkDay = SWITCH(
    TRUE(),
    ISBLANK('Calendar'[IsHoliday])
    && 'Calendar'[Day of Week] <> 0
    && 'Calendar'[Day of Week] <> 6 , "2",
    ISBLANK('Calendar'[IsHoliday])
    && 'Calendar'[Day of Week] = 0
    || 'Calendar'[Day of Week] = 6 , "1",
    "1")

Rank = RANKX(FILTER('Calendar','Calendar'[IsWorkDay]=2),'Calendar'[FullDateAlternateKey],,ASC,Dense)

Add3Days = LOOKUPVALUE('Calendar'[FullDateAlternateKey],'Calendar'[IsWorkDay],2,'Calendar'[Rank],'Calendar'[Rank]+3)

Thank you,

[Anonymous]

@TSP_PowerBi 

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.

[TSP_PowerBi]

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.

[mahoneypat]

Please see this video for how to do this (in M or DAX).

(14) Power BI - Tales from the Front #03 - Due Date From Working Days - YouTube

Pat

[TSP_PowerBi]

I have watched this video and it doesnt work for what I am trying to do. I saw that you posted this on another thread. I am not sure you read my post.

[mahoneypat]

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

[TSP_PowerBi]

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.

[mahoneypat · Accepted solution]

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
Source

Pat