Forum Discussion

cbtfs's avatar
cbtfs
New Member
3 years ago
Solved

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?

Need Help
  • Greg_Deckler's avatar
    Greg_Deckler
    3 years ago

    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

     

     

4 Replies

  • Greg_Deckler's avatar
    Greg_Deckler
    Community Champion
    3 years ago

    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
    • cbtfs's avatar
      cbtfs
      New Member
      3 years ago

      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!

      • Greg_Deckler's avatar
        Greg_Deckler
        Community Champion
        3 years ago

        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