I am trying to remove dates from column [Appt Date] that are more than 3 months old and would like it to work on rolling 3 months so that I do not have to continuously go into the code and update the date. Below is the code I use to replace values that are pre-2023 which works perfectly.
= Table.ReplaceValue(#"Capitalized Each Word", each[Appt Date], each if Date.Year(Date.From([Appt Date])) < 2023 then null else [Appt Date], Replacer.ReplaceValue, {"Appt Date"})
I have done some searching online and tried to adapt some solutions to meet my requirements but had no success.
today=DateTime.LocalNow(),
yr = Date.Year(today),
mnth = Date.Month(today),
dy = Date.Day(today),
prev= #date(yr, mnth-3,dy),
= Table.ReplaceValue(#"Capitalized Each Word", each[Appt Date], each if Date.Year(Date.From([Appt Date])) < prev then null else [Appt Date], Replacer.ReplaceValue, {"Appt Date"})
This example doesn't return any expression errors or anything but dates older than 3 months are still showing in the [Appt Date] Column.
Any assitance/guidance will be greatly appreciated.
Remove the Date.Year function as you are currently comparing a year number to an actual date. (I'm slightly surprised you have any data left actually) – Rory Nov 30 at 9:36