cancel
Showing results for 
Search instead for 
Did you mean: 
PriscillaCamp

A quick trick using the Relative Date Filter in Power BI

 

PriscillaCamp_1-1617722127278.png

 

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.

 

PriscillaCamp_3-1617667171137.png

 

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.

 

PriscillaCamp_4-1617667423094.png

 

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.

 

PriscillaCamp_5-1617667543201.png

 

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

 

PriscillaCamp_6-1617667726535.png

 

3. Now select “Apply Filter”

PriscillaCamp_7-1617667894001.png

 

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

 

PriscillaCamp_8-1617667967789.png

 

5. Now select Publish.

 

PriscillaCamp_9-1617668037614.png

 

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.

 

PriscillaCamp_10-1617668094041.png

 

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.

 

PriscillaCamp_11-1617668270232.png

 

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,

 

 

 

 

Comments

This will work for exactly 13 months from this exact day. In some cases it might be necessary to go "back 13 months from last data point date" instead of today. I'll try to briefly explain a solution for achieving the latter.

 

Let's say the last data point is found in 'Sales' table in column [Date] and there is a relationship between 'Date' table. Both columns need to be formatted as date datatype for the below function to work.

 

The approach:
Use a calculated column in the date table which is something like the following:
Monthly distance from last sales date = DATEDIFF('Date'[Date],  MAX('Sales'[Date]), MONTH)

 

This will calculate a monthly distance as integer in the column, which you can use in a similar fashion as the relative date when used as a visual level filter so that [Monthly distance from last sales date] is greater or smaller than...