How cool would it be to not have to manually update your dataset to keep data for last 2 Years (last year and this year to the current date)?

In this blog post I will show you how you can easily filter dates in Power Query to show dates for last year and year to date by using the GUI and not having to hardcode anything.

I must admit the first time I saw this was when I watching my good friend Reid Havens presenting. Thanks Reid!

Below are the steps to get it done to show how I completed the configuration:

  • Last Year
  • Year to date

Making the changes

  • I went into Power Query
  • I went to my table where I had my Date column.
    • In my example it was the column called Date
  • I then made sure that the Data Type for the column was set to Date
  • I then click on the down arrow and select Date Filters (1) and then Custom Filter…(2) on the bottom
  • When the Filter Rows Window pops up, I then clicked on Advanced
  • Now, this is where the magic happens, I am first going to configure it for Last Year
    • Under Operator where it says “equals” I click on the drop down and select “is in year”
    • Then under Value I then selected “Last Year”
    • The first row I have configured for Last Year looks like the following below.
  • Next, I am going to configure it for Year to Date.
    • On the next line I change the second condition from And to Or
    • Under Operator where it says “equals” I click on the drop down and select “is in year”
    • Then under Value I then selected “Year To Date”
    • After configuring both rows I now see the following in the Filter Rows window
  • I then clicked Ok
  • Now when I look at my table, I can see that the starting date for my table is the start of last year which is 01/01/2019
  • Likewise for me to check that it only has got the dates up until todays date (2020-08-31)
    • I did this by sorting the table descending

Summary

In this blog post I have shown how I configured my dataset to always keep my data current with last year’s data and this year’s data.

By using the built-in features of Power Query, I do not have to update anything manually and it will automatically update.

Thanks for reading, comments and suggestions are most welcome.