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

Compete to become Power BI Data Viz World Champion! First round ends August 18th. Get started.

Reply
cbtfs
New Member

Add working days to date

Hello,

 

I am trying to create a custom column that will calculate a "due date".  I have a start date column and a column with the number of working days that I want to add to the start date to produce the due date.  

 

Any thoughts?

1 ACCEPTED SOLUTION

@cbtfs Maybe a custom function like this:

 

 

let WorkDayAdd = (Start as date, WorkDays as number) as date =>
    let
        Source = List.Generate( () => Date.AddDays(Start, WorkDays * 4), each _ >= Date.AddDays(Start,1), each Date.AddDays(_, -1 )),
        #"Converted to Table" = Table.FromList(Source, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
        #"Added Custom" = Table.AddColumn(#"Converted to Table", "WeekDay", each Date.DayOfWeek([Column1],Day.Monday)),
        #"Filtered Rows" = Table.SelectRows(#"Added Custom", each [WeekDay] < 5),
        #"Sort Table" = Table.Sort(#"Filtered Rows", {"Column1"}),
        #"Added Index" = Table.AddIndexColumn(#"Sort Table", "Index", 1, 1, Int64.Type),
        #"Filtered Rows 1" = Table.SelectRows(#"Added Index", each [Index] = WorkDays),
        #"Select Column" = Table.SelectColumns(#"Filtered Rows 1", "Column1")
    in
        Record.Field(Table.Max(#"Select Column", "Column1"), "Column1")
in
    WorkDayAdd

 

 



Follow on LinkedIn
@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
DAX For Humans

DAX is easy, CALCULATE makes DAX hard...

View solution in original post

4 REPLIES 4
Greg_Deckler
Community Champion
Community Champion

@cbtfs I realize that this is not Power Query but perhaps this DAX solution will allow you to create an equivalent in PQ.

Due Date = 
    VAR __Days = [Work Days] * 2
    VAR __Calendar = 
        FILTER(
            ADDCOLUMNS(
                CALENDAR( [Start Date], [Start Date] + __Days),
                "WeekDay",WEEKDAY([Date],2)
            ),
            [WeekDay] < 6
        )
    VAR __Table = 
        ADDCOLUMNS(
            __Calendar,
            "Index", COUNTROWS(FILTER(__Calendar, [Date] <= EARLIER([Date])))
        )
    VAR __Result = MAXX(FILTER(__Table, [Index] = [Work Days]),[Date])
RETURN
    __Result


Follow on LinkedIn
@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
DAX For Humans

DAX is easy, CALCULATE makes DAX hard...

Hi Greg,

Thanks for the info, but I am specifically looking for a solution within Power Query.  I don't have any experience with DAX unfortunately and am looking to add this feature onto an existing power query.

Thanks!

@cbtfs Maybe a custom function like this:

 

 

let WorkDayAdd = (Start as date, WorkDays as number) as date =>
    let
        Source = List.Generate( () => Date.AddDays(Start, WorkDays * 4), each _ >= Date.AddDays(Start,1), each Date.AddDays(_, -1 )),
        #"Converted to Table" = Table.FromList(Source, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
        #"Added Custom" = Table.AddColumn(#"Converted to Table", "WeekDay", each Date.DayOfWeek([Column1],Day.Monday)),
        #"Filtered Rows" = Table.SelectRows(#"Added Custom", each [WeekDay] < 5),
        #"Sort Table" = Table.Sort(#"Filtered Rows", {"Column1"}),
        #"Added Index" = Table.AddIndexColumn(#"Sort Table", "Index", 1, 1, Int64.Type),
        #"Filtered Rows 1" = Table.SelectRows(#"Added Index", each [Index] = WorkDays),
        #"Select Column" = Table.SelectColumns(#"Filtered Rows 1", "Column1")
    in
        Record.Field(Table.Max(#"Select Column", "Column1"), "Column1")
in
    WorkDayAdd

 

 



Follow on LinkedIn
@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
DAX For Humans

DAX is easy, CALCULATE makes DAX hard...

Hi! Came accross this post and this worked in power query! What if i also dont want it to count a holiday along with weekends. How can i adjust the custom function to also disregard holidays?

 

Thanks!

Helpful resources

Announcements
August Power BI Update Carousel

Power BI Monthly Update - August 2025

Check out the August 2025 Power BI update to learn about new features.

August 2025 community update carousel

Fabric Community Update - August 2025

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

Top Solution Authors