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.
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.
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.
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.
8 thoughts on “Relative Date Slicer in Power BI; Simple, yet Powerful”
In the Service the dates are based on UTC time, we use reports which are built into dashboards to review the last 24 hrs, week, month etc. The slicer then changes the report at midday when its UTC midnight, frustrating for users as the report is different in the morning and afternoon. Have tried lots of work arounds, really need a slicer that you can set the offset in. Any ideas welcome.
I have written an article about how to solve the timezone issue here.
This is very relevant as I have just started looking at this.
We are having issues with the fact that the relative date slicer works on UTC time in PBI Service, so (in Australia AEST) we are not seeing the current days date until 10am. Do you know of a way we can resolve this?
Hi Carl, please read my blog article about the time zone.
We (and Microsoft) have tried these solutions and many others, seems the solution requires the offset to be applied to the slicer for the relative date slicer to work using NZDT in the Service.
Keen to find a solution as we currently have a work around that creates a number of days offset, then two refreshes of the datasets per day to make reports correct morning and afternoon,
No where near as good as having the relative date slicer working for NZDT.
Any other ideas or solutions welcome?
Seems lots of demand for this fix with over 400 votes:
currently not on the Microsoft roadmap for a fix as it is working as designed…..
you can do that with adding offset columns into your date table, and use those in a slicer. I might write a blog about that.
That would be fantastic to see this solution.
We set up a simple file to try all the ideas we had and found on the web.
If you can get the value to be 6 in the morning and afternoon in the Service, then 7 when viewed tomorrow using the relative date slicer, set to last 1 day that would be amazing.
I explained a solution for the relative date slicer considering the local timezone here.