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 ...
Need Help
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,