Relative Date Slicer in Power BI; Simple, yet Powerful

If you are using a date slicer in your Power BI report, a relative date slicer is one of the options to consider for sure. This is a very simple type of slicer to use, but very effective in terms of options that it provides for the user to slice and dice the data. Let’s check it out in this short article.

Relative Date Slicer

This type of slicer can be used when you have assigned a date field to the slicer in Power BI.

Choosing the type of slicer is in the slicer header, so if you can’t see this option, better to check and see if your Slicer Header property is On.

This is how easy you can access the Relative Date slicer. This type of slicer, simply gives you the ability to filter the data based on a relative date to today’s date.

For example, you can choose the last few period, but selecting Last, the number of periods you want to have included in your filter, and then selecting the period.

The above slicer, is getting the last three months of data from 5th of Feb 2020 to 4th of May 2020. Considering that today is 5th of May 2020.

This is a very simple way to filter your report for things such as last week, last month, last three months, etc.

Calendar vs Standard

If you choose Months (Calendar), then the period always consider full calendar months. which means it will start from 1st of Feb until end of April, because May is not still a full calendar month (considering today is 5th of May).

Current Period vs Past vs Future

You can filter your data to be in the past, future, or current using the first drop down of this slicer;

You can use this switch to create a report of the current year, month, week, etc. or even future (if you have that data in your dataset).

Relative Date Filter

The relative date option is also available for Filter pane. If you have a filter on visual, or page, or all the pages, with a Date field, you can change the filter type to be Relative Date.

And this will lead you to the Relative Date Filter which gives you exactly the same features.

Include Today

In the screenshot above in the Relative Date Filter you have seen that it also has the option to include today or not.

The same option is available for the Relative Date Slicer, in the Date Range property of the slicer.

Anchor Date

It is probable that you have a specific date, which you want the relative dates to be based on that as an anchor date. let’s say that is the fruit picking date etc. You can set the Anchor Date in the Date Range settings.

Summary

Relative date filtering in a Power BI report is very simple using the Relative Date Slicer or Relative Date Filter, it gives you options to go back and forth on the selected period range from an anchor date with some extra options. Have you been using this slicer type? if yes, tell me about your experience, if no, tell me what you want which can’t be done using this slicer. I like to hear about your experience in the comments below.

Video

Reza Rad

Trainer, Consultant, Mentor

Reza Rad is a Microsoft Regional Director, an Author, Trainer, Speaker and Consultant. He has a BSc in Computer engineering; he has more than 20 years’ experience in data analysis, BI, databases, programming, and development mostly on Microsoft technologies. He is a Microsoft Data Platform MVP for 12 continuous years (from 2011 till now) for his dedication in Microsoft BI. Reza is an active blogger and co-founder of RADACAD. Reza is also co-founder and co-organizer of Difinity conference in New Zealand, Power BI Summit, and Data Insight Summit.
Reza is author of more than 14 books on Microsoft Business Intelligence, most of these books are published under Power BI category. Among these are books such as Power BI DAX Simplified, Pro Power BI Architecture, Power BI from Rookie to Rock Star, Power Query books series, Row-Level Security in Power BI and etc.
He is an International Speaker in Microsoft Ignite, Microsoft Business Applications Summit, Data Insight Summit, PASS Summit, SQL Saturday and SQL user groups. And He is a Microsoft Certified Trainer.
Reza’s passion is to help you find the best data solution, he is Data enthusiast.
His articles on different aspects of technologies, especially on MS BI, can be found on his blog: https://radacad.com/blog.