Forum Discussion

mweyri01's avatar
mweyri01
New Member
2 years ago
Solved

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 th...
Need Help
  • Anonymous's avatar
    Anonymous
    2 years ago

    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

Syndicate_Admin's avatar
Syndicate_Admin
Icon for Administrator rankAdministrator
1 year ago

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