Forum Discussion
Add Business Days to Date Column with Duration Column
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 |
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 __resultBest Regards,
Gao
Community Support TeamIf 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
4 Replies
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 __resultBest Regards,
Gao
Community Support TeamIf 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
Hi Anonymous
Thank you for your help! This worked for me.
- AnalyticPulse
Solution Sage
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)https://analyticpulse.blogspot.com
Syndicate_AdminAdministrator
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
