Relative Date Slicer in Power BI; Simple, yet Powerful

FacebooktwitterredditpinterestlinkedintumblrmailFacebooktwitterredditpinterestlinkedintumblrmail
FacebooktwitterlinkedinrssyoutubeFacebooktwitterlinkedinrssyoutube

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

FacebooktwitterredditpinterestlinkedintumblrmailFacebooktwitterredditpinterestlinkedintumblrmail
FacebooktwitterlinkedinrssyoutubeFacebooktwitterlinkedinrssyoutube
Reza Rad on FacebookReza Rad on LinkedinReza Rad on TwitterReza Rad on Youtube
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 nine 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.
His articles on different aspects of technologies, especially on MS BI, can be found on his blog: https://radacad.com/blog.
He wrote some books on MS SQL BI and also is writing some others, He was also an active member on online technical forums such as MSDN and Experts-Exchange, and was a moderator of MSDN SQL Server forums, and is an MCP, MCSE, and MCITP of BI. He is the leader of the New Zealand Business Intelligence users group. He is also the author of very popular book Power BI from Rookie to Rock Star, which is free with more than 1700 pages of content and the Power BI Pro Architecture published by Apress.
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.

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.

  • Hi,
    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?
    Carl

  • Hi Reza,

    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:
    https://ideas.powerbi.com/forums/265200-power-bi-ideas/suggestions/32123752-power-bi-service-recognize-local-time-zone-for-tod
    https://ideas.powerbi.com/forums/265200-power-bi-ideas/suggestions/35610880-fix-releative-date-slicer-utc-issue

    currently not on the Microsoft roadmap for a fix as it is working as designed…..

    • Hi Richard
      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.
      Cheers
      Reza

      • Hi Reza,

        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.

        Date Value
        1/5. 1
        2/5. 2
        3/5. 3
        4/5. 4
        5/5. 5
        6/5. 6
        7/5. 7

        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.

        Thanks Richard

Leave a Reply

%d bloggers like this: