topic Re: Add business days to a date based on a value from another column in DAX Commands and Tips

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

rachaelwalker — Wed, 22 Mar 2023 15:14:29 GMT

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.

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

tamerj1 — Wed, 22 Mar 2023 16:25:58 GMT

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] )

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

rachaelwalker — Thu, 23 Mar 2023 18:30:52 GMT

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

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

tamerj1 — Fri, 24 Mar 2023 03:09:08 GMT

rachaelwalker

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

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

rachaelwalker — Tue, 28 Mar 2023 15:25:00 GMT

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