Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
mweyri01
New Member

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
1 ACCEPTED SOLUTION
v-cgao-msft
Community Support
Community Support

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]

vcgaomsft_0-1716260123920.png

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

vcgaomsft_1-1716260208002.png

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

View solution in original post

3 REPLIES 3
v-cgao-msft
Community Support
Community Support

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]

vcgaomsft_0-1716260123920.png

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

vcgaomsft_1-1716260208002.png

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

Hi @v-cgao-msft 

 

Thank you for your help! This worked for me. 

AnalyticPulse
Impactful Individual
Impactful Individual

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

AnalyticPulse_0-1715829624787.png

 

Helpful resources

Announcements
Europe Fabric Conference

Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

Power BI Carousel June 2024

Power BI Monthly Update - June 2024

Check out the June 2024 Power BI update to learn about new features.

PBI_Carousel_NL_June

Fabric Community Update - June 2024

Get the latest Fabric updates from Build 2024, key Skills Challenge voucher deadlines, top blogs, forum posts, and product ideas.

Top Solution Authors