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

Resolver III

## 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 III

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 III

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 III

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

If you love stickers, then you will definitely want to check out our Community Sticker Challenge!

#### Fabric Community Update - July 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors
Top Kudoed Authors