Add Business Days to Date Column with Duration Column

[mweyri01]

Hi there,

I have a column named "Start Date" formatted as a date and a column named "Duration" formatted as a whole number. I am looking to create a column named "End Date" which adds the adds the number of business days (Monday-Friday) from the "Duration" column to the "Start Date" column. 

Any help would be greatly appreciated! 

For example:

Start Date		Duration		DESIRED OUTPUT
Monday, April 15, 2024		4		Friday, April 19, 2024
Wednesday, May 1, 2024		6		Thursday, May 9, 2024
Wednesday, June 5, 2024		10		Wednesday, June 19, 2024
Sunday, March 17, 2024		3		Wednesday, March 20, 2024

[Syndicate_Admin]

You can solve it in Power BI using Power Query and a custom function that adds only business days. A handy option is to use List.Dates to generate dates from the "Start Date", filter only the working days (Monday to Friday using Date.DayOfWeek), and then take the corresponding date based on the duration. Here's the focus:

Open Power Query.

Add a custom column with this code:

startDate = [Fecha de inicio],
duration = [Duración],
datesList = List.Dates(startDate, duration * 2, #duration(1,0,0,0)),
workdays = List.Select(datesList, each Date.DayOfWeek(_, Day.Monday) <= 4),
endDate = workdays{duration - 1}
in
endDate

[Anonymous · Accepted solution]

Hi @mweyri01 ,

Here's my solution.
1. add 2 calculated column in 'date' table:

Working Day = IF ( NOT WEEKDAY ( 'Date'[Date], 2 ) IN { 6, 7 }, TRUE(), FALSE() )

Working Day Number = 
RANKX (
    FILTER (
        'Date',
        'Date'[Working Day]
    ),
    'Date'[Date],
    ,
    ASC
) - NOT 'Date'[Working Day]

add a calculated column in 'table':

End Date = 
VAR __rank = RELATED('Date'[Working Day Number])
VAR __working_day_number = __rank + 'Table'[Duration]
VAR __result = CALCULATE(MIN('Date'[Date]),FILTER(ALL('Date'),'Date'[Working Day Number] = __working_day_number && 'Date'[Working Day]))
RETURN
__result

Best Regards,
Gao
Community Support Team

If there is any post helps, then please consider Accept it as the solution  to help the other members find it more quickly.
If I misunderstand your needs or you still have problems on it, please feel free to let us know. Thanks a lot!

How to get your questions answered quickly --  How to provide sample data in the Power BI Forum -- China Power BI User Group

[mweyri01]

Hi @Anonymous 

Thank you for your help! This worked for me. 

[AnalyticPulse]

hi @mweyri01 
i have assumed your table name as Current_table 
try below dax:

End Date =
VAR _startDate = Current_table[Start Date]
VAR _duration = Current_table[Duration]

VAR _endDate =
_startDate +
INT((_startDate + _duration + 5) / 7) * 2 +
MOD((_startDate + _duration), 7) - MOD(_startDate, 7)

RETURN
_endDate + IF(MOD(_endDate, 7) > 4, 2, 0)

Learn Power BI free:

https://analyticpulse.blogspot.com

Power BI : getting started

Dax functions

powerbi Visualisation