Using the date filter in power query dynamically

[akhaliq7]

In power query i have loaded my fact table the next step i want is to filter the rows by a date column but wanted a solution to returning rows based on the past 2 years, this year and the next 5 years but as dynamic as possible don't want to hardcode the values as the report will need to be updated every year.

The only solution I discovered currently was you can only give 2 rules for dynamic date filtering. 

[BA_Pete · Accepted solution]

Hi @akhaliq7 ,

Your filter step would look something like this:

=Table.SelectRows(
  previousStepName,
  each [Date] >= Date.StartOfYear(Date.AddYears(Date.From(DateTime.LocalNow()), -2))
    and [Date] <= Date.EndOfYear(Date.AddYears(Date.From(DateTime.LocalNow()), 5))
)

Pete