Relative Date Filters

One of my favorite Power BI report authoring features added this year is the ability to define relative date filters both as slicers and off canvas at all filter scopes (visual level, page level, and report level). The feature, available to date and date/time columns, makes it easy to focus reports on alternative time frames and can sometimes serve as a lightweight alternative to dynamic date columns and custom DAX measures.

5-Page Example: Then, Now, Next

As one simple example, you could divide a report into five pages with a page level filter for each page providing a different time frame:

The five page level filters take advantage of the three relative date filter conditions (in the last, in this, in the next). Let’s look at the five filter conditions as of today (2017-11-30):

For the “is in the last” and “is in the next” filter conditions, calendar week, calendar month, and calendar year conditions are available as well. For example, if you only needed the last 12 completed months, you could filter for the last 12 calendar months. As of today, this would retrieve November of 2016 through October of 2017.

Relative Date Slicers

The same date conditions are also available as slicers:

Wrapping Up

As you’d expect with something as universal as filtering by dates, there are many different use cases for this feature. For example, you could apply relative date filters to one or multiple date columns from dimension tables other than the date table (e.g. Product Start Date, Promotion End Date). You could also apply different time frame filter conditions per visual within a report page.

At a minimum, especially when you’re not tied to a non-standard or fiscal calendar, relative date filters help address many of the most common date filter conditions.