Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
6 years ago

Adding 3 days to a Specific Date

I need to calculate 3 days from the application date and exclude weekends.

 

I have a date table that I created in Power Query that specifies the day of the week. 0-6

If I need to add a column to that table in PowerQuery, I am unsure how to do so.

 

ApplicationDate + 3days(Excluding Weekends) = DesiredDate

 

Any ideas?

Need Help

4 Replies

  • mahoneypat's avatar
    mahoneypat
    Icon for Microsoft Employee rankMicrosoft Employee
    6 years ago

    You can do it in the query editor by adding a custom column with the following formula.  Based on your day of the week number column, it finds the # of days to add to your date.

     

    = Date.AddDays([Date], {3,3,3,5,5,5,4}{[Day of Week]})

     

    Below is an example query that shows it in use.  To see how it works, just create a blank query, go to Advanced Editor, and replace the text there with the M code below.

     

    let
    Source = List.Dates(#date(2020,1,1),30, #duration(1,0,0,0)),
    #"Converted to Table" = Table.FromList(Source, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
    #"Changed Type" = Table.TransformColumnTypes(#"Converted to Table",{{"Column1", type date}}),
    #"Renamed Columns" = Table.RenameColumns(#"Changed Type",{{"Column1", "Date"}}),
    #"Inserted Day of Week" = Table.AddColumn(#"Renamed Columns", "Day of Week", each Date.DayOfWeek([Date]), Int64.Type),
    #"Inserted Day Name" = Table.AddColumn(#"Inserted Day of Week", "Day Name", each Date.DayOfWeekName([Date]), type text),
    #"Added Custom" = Table.AddColumn(#"Inserted Day Name", "Custom", each Date.AddDays([Date], {3,3,3,5,5,5,4}{[Day of Week]}))
in
    #"Added Custom"

     

    If this works for you, please mark it as the solution.  Kudos are appreciated too.  Please let me know if not.

    Regards,

    Pat

    • Anonymous's avatar
      Anonymous
      Not applicable
      6 years ago

      This would have otherwise worked, but the DateAdd function doesn't work in DirectQuery.

       

      Every table except the date table is a direct query from SQL.

       

      Could I do the DateAdd in the date table and it still work correctly with the direct query date?

       

      I am trying to enter this into DAX, and I get this error. If I input just 3 it works, but not the string.

       

       

      Thank you,

    • Anonymous's avatar
      Anonymous
      Not applicable
      6 years ago

      This is not very specific in how to exclude weekends.