cancel
Showing results for
Did you mean:

Fabric is Generally Available. Browse Fabric Presentations. Work towards your Fabric certification with the Cloud Skills Challenge.

Resolver II

## Add business days to a date based on a value from another column

I want to add business days to a date based on the value in another column. I successfully added calendar days in power query using Date.AddDays but I need it to add using business days

I have two related tables: Project and Calendar. I am trying to solve for Target Date adding business days only. See sample data and expected results below.

[Target Date] = [PO Date] + [SLA]

Project Table

 Project ID Date Created* PO Date SLA Target Date 1234 5/19/2022 5/20/2022 5 5/27/2022 1235 5/21/2022 5/23/2022 5 5/30/2022 1236 5/21/2022 5/25/2022 7 6/3/2022 1237 5/24/2022 5/31/2022 10 6/14/2022 1238 6/1/2022 6/2/2022 5 6/8/2022 1239 6/3/2023 6/3/2022 5 6/10/2022

Calendar Table

 Date* Business Day 5/20/2022 Friday 5/21/2022 Saturday 5/22/2022 Sunday 5/23/2022 Monday 5/24/2022 Tuesday 5/25/2022 Wednesday 5/26/2022 Thursday 5/27/2022 Friday 5/28/2022 Saturday 5/29/2022 Sunday 5/30/2022 Monday 5/31/2022 Tuesday 6/1/2022 Wednesday 6/2/2022 Thursday 6/3/2022 Friday 6/4/2022 Saturday

* relationship

I am following this video https://www.youtube.com/watch?v=2HkBbqxBzF0 but I have not been successful.

2 ACCEPTED SOLUTIONS
Super User

Target Date =
VAR PODate = [PO Date]
VAR N = [SLA] + 1
VAR Dates =
FILTER (
ALL ( 'Calendar'[Date] ),
'Calendar'[Date] >= PODate
&& NOT ( WEEKDAY ( 'Calendar'[Date], 2 ) IN { 6, 7 } )
)
VAR FirstNDates =
TOPN ( N, Dates, 'Calendar'[Date], ASC )
RETURN
MAXX ( FirstDates, 'Calendar'[Date] )

Resolver II

Thank you! I was able to get it to work using similar way you have outlined!

Target Date =
VAR StartDate = 'Project'[PO Date]
VAR NextWorkingDates =
FILTER (
ALL ( 'Calendar' ),
'Calendar'[Date] > StartDate
)
VAR WorkingDaysPlusSLA =
TOPN (
'Project'[SLA],
NextWorkingDates,
'Calendar'[Date], ASC
)
VAR Result =
MAXX ( WorkingDaysPlusSLA, 'Calendar'[Date] )
RETURN
Result
4 REPLIES 4
Super User

Target Date =
VAR PODate = [PO Date]
VAR N = [SLA] + 1
VAR Dates =
FILTER (
ALL ( 'Calendar'[Date] ),
'Calendar'[Date] >= PODate
&& NOT ( WEEKDAY ( 'Calendar'[Date], 2 ) IN { 6, 7 } )
)
VAR FirstNDates =
TOPN ( N, Dates, 'Calendar'[Date], ASC )
RETURN
MAXX ( FirstDates, 'Calendar'[Date] )

Resolver II

Thank you! I was able to get it to work using similar way you have outlined!

Target Date =
VAR StartDate = 'Project'[PO Date]
VAR NextWorkingDates =
FILTER (
ALL ( 'Calendar' ),
'Calendar'[Date] > StartDate
)
VAR WorkingDaysPlusSLA =
TOPN (
'Project'[SLA],
NextWorkingDates,
'Calendar'[Date], ASC
)
VAR Result =
MAXX ( WorkingDaysPlusSLA, 'Calendar'[Date] )
RETURN
Result
Super User

Just be careful if [SLA] = 0 days, TOPN will generate an error

Resolver II

The SLA value should never be 0 but thank you for calling that out for awareness. I will double double check that! Thank you!

Announcements

#### Power BI Monthly Update - November 2023

Check out the November 2023 Power BI update to learn about new features.

#### Fabric Community News unified experience

Read the latest Fabric Community announcements, including updates on Power BI, Synapse, Data Factory and Data Activator.

#### Exclusive opportunity for Women!

Join us for a free, hands-on Microsoft workshop led by women trainers for women where you will learn how to build a Dashboard in a Day!

#### The largest Power BI and Fabric virtual conference

130+ sessions, 130+ speakers, Product managers, MVPs, and experts. All about Power BI and Fabric. Attend online or watch the recordings.

Top Solution Authors
Top Kudoed Authors