Forum Discussion

PhilipLamour's avatar
PhilipLamour
Regular Visitor
3 years ago

Creating a Cut off date

I am attempting to create a query that uses calendar dates to highlight when a "cutoff" is supposed to happen each month.  The Cutt off date is the 20th of each month, however is the 20th falls on a weekend or a Holiday the "Cut Off" will be the business day prior.  So If the 20th is on a sunday the cut off date will be that Friday th 18th.  However if the 20th is on a sunday but we have a holiday on the that Friday and Thursday the Cut off would be the 16th (Wednesday). 

I created a calendar query with dates from 1/1/21 to 12/31/25, I added a column which shows holidays as well as another column that shows the day of the week.  

Where I am stuck is creating a conditional column that will set the Cutoff date correctly if the 20th of the month falls on the weekend, or a holiday.  

Any ideas?  

Need Help
Tips and Tricks

1 Reply

  • Anonymous's avatar
    Anonymous
    Not applicable
    3 years ago

    Hi PhilipLamour - The following code can be adapted if you can provide a list of the Holidays.

    let
    #"List of Holidays" = { #date(2021,1,1) , #date(2022,1,1), #date(2023,1,1) } ,
    Source = List.Dates( #date(2021,12,31), 365 * 5 + 1, #duration(1,0,0,0)  ),
    #"Converted to Table" = Table.FromList(Source, Splitter.SplitByNothing(), type table[Date = Date.Type]),
    #"Add Weekends" = Table.AddColumn(#"Converted to Table", "IsWeekend", each Date.DayOfWeek([Date]) = Day.Saturday or Date.DayOfWeek( [Date]) = Day.Sunday, type logical),
    #"Added Custom" = Table.AddColumn(#"Add Weekends", "IsHoliday", each List.Contains( #"List of Holidays" , [Date] ), type logical),
    #"Inserted Start of Month" = Table.AddColumn(#"Added Custom", "Start of Month", each Date.StartOfMonth([Date]), type date),
    #"List of Cut Off Dates" = 
        Table.Group(
            #"Inserted Start of Month",
            {"Start of Month"}, 
            {
                {
                    "CutOffDate", 
                        (x) => 
                            List.Max( 
                                Table.SelectRows( 
                                    x , 
                                    each Date.Day([Date]) <= 20 and not [IsWeekend] and not [IsHoliday] 
                                )[Date]
                            ), 
                    type date 
                } 
            }
        )[CutOffDate],
    #"Add Cut Off Flag" = Table.AddColumn(#"Inserted Start of Month", "IsCutOffDate", each List.Contains( #"List of Cut Off Dates" , [Date] ) , type logical)
in
    #"Add Cut Off Flag"