Filtering Date/Time Data with Relative Dates - Previous, Year, Year-to-Date, etc.

The ‘Relative Date’ option in the Filter makes it easier to create the filtering conditions based on the current date and time.

With this option, for example, you can create the filtering conditions for ‘Year’. like the below.

  • Previous Year
  • This Year
  • Next Year
  • Last N Years
  • Year to Date

You can select ‘Relative Dates’ from the column header menu.

Or, select it in the Filter dialog.

Some might be obvious, but some might not. Let’s take a look at the difference among some of the following options.

  • Previous Year
  • This Year
  • Last N Years
  • Year to Date

Previous (Last) Year

Previous Year returns the data from January to December of the last year.

This Year

This Year returns the data from January to December of this year. The sample data below had only up to August (as of writing), this is why it’s showing from January to August.

Last N Years

This will return the data starting January and ending December of the last N year(s). Here’s an example ‘Last 1 Year’.

Year to Date

Year to Date returns the data starting from January of the current year (January) to ‘today’.

The sample below is showing only up to August, which is ‘today’ as of writing.

These are not only for the year, but also for Quarter, Month, Week, and Day as well.

If the data type is ‘POSIXct’ (Date with Time), then you can use Hour, Minute, Second, too!