Add business days to a date based on a value from another column

[rachaelwalker]

I want to add business days to a date based on the value in another column. I successfully added calendar days in power query using Date.AddDays but I need it to add using business days 

I have two related tables: Project and Calendar. I am trying to solve for Target Date adding business days only. See sample data and expected results below.

[Target Date] = [PO Date] + [SLA] 

Project Table

Project ID	Date Created*	PO Date	SLA	Target Date
1234	5/19/2022	5/20/2022	5	5/27/2022
1235	5/21/2022	5/23/2022	5	5/30/2022
1236	5/21/2022	5/25/2022	7	6/3/2022
1237	5/24/2022	5/31/2022	10	6/14/2022
1238	6/1/2022	6/2/2022	5	6/8/2022
1239	6/3/2023	6/3/2022	5	6/10/2022

Calendar Table

Date*	Business Day
5/20/2022	Friday
5/21/2022	Saturday
5/22/2022	Sunday
5/23/2022	Monday
5/24/2022	Tuesday
5/25/2022	Wednesday
5/26/2022	Thursday
5/27/2022	Friday
5/28/2022	Saturday
5/29/2022	Sunday
5/30/2022	Monday
5/31/2022	Tuesday
6/1/2022	Wednesday
6/2/2022	Thursday
6/3/2022	Friday
6/4/2022	Saturday

* relationship

I am following this video https://www.youtube.com/watch?v=2HkBbqxBzF0 but I have not been successful. 

[tamerj1 · Accepted solution]

Hi @rachaelwalker 

please try

Target Date =
VAR PODate = [PO Date]
VAR N = [SLA] + 1
VAR Dates =
FILTER (
ALL ( 'Calendar'[Date] ),
'Calendar'[Date] >= PODate
&& NOT ( WEEKDAY ( 'Calendar'[Date], 2 ) IN { 6, 7 } )
)
VAR FirstNDates =
TOPN ( N, Dates, 'Calendar'[Date], ASC )
RETURN
MAXX ( FirstDates, 'Calendar'[Date] )

[tamerj1 · Accepted solution]

Hi @rachaelwalker 

please try

Target Date =
VAR PODate = [PO Date]
VAR N = [SLA] + 1
VAR Dates =
FILTER (
ALL ( 'Calendar'[Date] ),
'Calendar'[Date] >= PODate
&& NOT ( WEEKDAY ( 'Calendar'[Date], 2 ) IN { 6, 7 } )
)
VAR FirstNDates =
TOPN ( N, Dates, 'Calendar'[Date], ASC )
RETURN
MAXX ( FirstDates, 'Calendar'[Date] )

[rachaelwalker · Accepted solution]

Thank you! I was able to get it to work using similar way you have outlined!

Target Date =

VAR StartDate = 'Project'[PO Date]

VAR NextWorkingDates =

    FILTER (

        ALL ( 'Calendar' ),

        'Calendar'[Date] > StartDate

            && 'Calendar'[DayofBusiness] = TRUE

    )

VAR WorkingDaysPlusSLA =

    TOPN (

        'Project'[SLA],

        NextWorkingDates,

        'Calendar'[Date], ASC

    )

VAR Result =

    MAXX ( WorkingDaysPlusSLA, 'Calendar'[Date] )

RETURN

    Result

[tamerj1]

@rachaelwalker 

Just be careful if [SLA] = 0 days, TOPN will generate an error

[rachaelwalker]

The SLA value should never be 0 but thank you for calling that out for awareness. I will double double check that! Thank you!