A quick trick using the Relative Date Filter in Power BI

[PriscillaCamp]

To help you understand my blog, below is the Date Dimension which is marked as a Date Table in Power BI Desktop. This date table includes every date from 2016-2025. I do have more columns in my Date Dimension, but I only want to show the ones necessary for this example. As you can see, I have a Date Column and a Month Year column. For my report, only the Month and Year Column is needed for filtering.

Problem

Most of my reports at work are manually updated every month to reflect a rolling 13 months (Oct 2019 – Oct 2020) as shown above. Updating these reports to this setting was a pain, because I had to open and refresh all of my reports to do this. Often, I would spend 2 hours rolling all my reports forward. This quickly turned out to be burdensome and a waste of time, so I needed to find a way to have it update automatically. The challenge about these reports is the rolling 13 months needs to be displayed on the visualizations, but the filter needs to include other months so users can still slice through them. While researching this problem, I found solutions which pointed to using the relative date feature which works. But if you add the same month field to the filter pane, it will now show Oct 2019 – Oct 2020. This is a major drawback because my users in the Power BI Service would not be able to filter data on months outside of the rolling 13 months, as shown below.

I thought is there a way to use the relative date feature, but still allow my users to have access to the months outside of the rolling 13 months? I played with this feature and was able to come up with a trick. Below is my solution and instructions on how you can do the same.

Solution

1. In the Filter Pane, go to the Month Filter. In the “Filter Type” field, select Relative Date.

2. In the “Show items when the value:” fields please enter the following selections:

1st field - Is in the last

2nd field - 13

3rd field - months

3. Now select “Apply Filter”

4. Go back top field called "Filter type" and select Basic Filtering.

5. Now select Publish.

6. In the Power BI Service, your users will see the defaulted rolling 13 months on the report, and still be able to filter through the other months.

Conclusion

As you can see, our users can still navigate through the other months, but my reports are now showing a rolling 13 months.

Please let me know if this works for you, and if you currently implementing another solution to the problem! Also, please watch my video, which is a supplement to this blog.

Notes:

This trick was based on a specific business requirement. I only needed my data to be shown at the month level. Therefore, using the month field with the relative date filter worked.

I did notice one odd behavior worth mentioning:

1. On the Month Filter, the date range will display as 9/5/2019 – 10/4/2020. However, if you look at the visualization it shows October 2019 to October 2020.

Thank you for reading my blog.

You can view my video which is a supplement to this blog at: Quick Trick: Relative Date Filter in Power BI - YouTube

To learn more about me you can visit my page or connect on Social Media:  https://ITDATADIVA.com , (21) Priscilla (Marotte) Camp | LinkedIn , https://twitter.com/ITDataDiva,