cancel
Showing results 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

New Member

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

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

3 REPLIES 3
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]``````

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

New Member

Thank you for your help! This worked for me.

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

Announcements

#### 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 Monthly Update - June 2024

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

#### 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
Top Kudoed Authors