When I create a new dashboard and if I don’t need the full data, I like to load data for only some years, it can be:

• Last year and this year
• 2 years ago
• 3 years ago and the current year
• Etc.

Of course, except the date, I can filter other conditions to reduce the size of my data (read this optional article Power BI: filter before loading data). So in the Power Query Editor, I have those options (left picture) and I will load only data from the last year (right picture):

As you can see in the left picture, I could choose to filter by quarters, by days, by minutes, etc. On the right picture, I will always load 2 years of data in an automatic way but one of the missing things is an “explicit” option to load data of the previous 2 full year or more.

Before to continue, if the date column has the ISO 8601 format (yyyy-MM-ddTHH:mm:ssZ):

Those filters will not be available because it is formatted as “text” instead of “date/time”. To have it to the correct format, click on “transform -> extract -> text before delimiter”:

In the popup, put just a dot in the “delimiter” field then click “OK”:

To end, click on “home -> data type -> date/time”:

Back to my explanation to get the previous 2 full year or more. Choosing again “is in year”, I have those choices:

None are 2 years ago for instance. Same thing for the other options. I can use “is after” or “is after or equal to” and put the year I want:

But in this case, Power BI will always load data from this date and in the future, if I need to reduce the size of my data again, I have to change it manually. For an automatic update, I will use “is in the previous”:

Using only this option, I will have only data of the last 2 years which doesn’t include the current year, to remediate it, I will add the “is in year” one like that:

What about 18 months with this option ? In this case, I will get only the last 18 months and to include the current month, I will add the “is in month = this month”:

Same logic applies for the other selections of “is in the previous”.

Amongst the options, there is no way to filter a specific date except to put it manually, for instance, if I want to filter all data of 2022, I have to select “2022” so I can’t select something like “YEAR - 3” but there is a way to do it. I will select “year -> this year”:

Then in the formula bar, replace the red section with: Date.Year([argument]) = Date.Year(DateTime.LocalNow())-number

NOTE:

• Replace “argument” and “number” by yours
• For quarter: Date.QuarterOfYear([argument]) = Date.QuarterOfYear(Date.AddQuarters(Date.From(DateTime.LocalNow()),-1))
• For month: Date.Month([argument]) = Date.Month(Date.AddMonths(Date.From(DateTime.LocalNow()),-number))
• For week: Date.WeekOfYear([argument]) = Date.WeekOfYear(Date.AddWeeks(Date.From(DateTime.LocalNow()),-number))
• For day: [argument] = Date.AddDays(Date.From(DateTime.LocalNow()),-number)

If I want between years, take the red section:

And put it in its corresponding section in this formula: List.Contains({red section, red section},Date.Year([argument]))

For quarter, month, week and day, just do the same process.