cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted

Dynamic Relative Date Filtering

Hi,

 

One of my go to features is creating a calculated column in my Date dimension to show my the Offset of months from the current month. So:

 

= DATEDIFF(TODAY(), DateTable[Date], MONTH)

 

Which will say October =0, September = -1, August = -2 and so on.

 

And it's great. I can pop it into a visual level filter and do less than or equal to 0 and greater or equal to -5 and it will give me the last 5 full months + the current incomplete month. Soemthing I believe relative date filtering can't do unless I'm mistaken.

 

 

However I'm wondering how easy it would be to replicate this but where the selected month always equals 0. So if I have a filter on the report for Month and the user selects March, then March becomes the "0" in this above instance and everything shifts back from there. So I could select a rolling 15 months for example and whenever a certain month is selected, it always shows the previous 15 months from that point.

 

 

1 ACCEPTED SOLUTION

Accepted Solutions
Super User
Super User

Re: Dynamic Relative Date Filtering

Hi @robstewart89,

 

Although this is a good workaround for the rolling months, this is not the best approach for this.

 

Check the blog below where you have an example of selecting the month and the N months you want to have displayed on your charts and this works with filter of dates.

 

http://sqljason.com/2018/03/display-last-n-months-selected-month-using-single-date-dimension-in-powe...

 

Regards,

MFelix

 



Did I answer your question? Mark my post as a solution!

Proud to be a Datanaut!




2 REPLIES 2
Super User
Super User

Re: Dynamic Relative Date Filtering

Hi @robstewart89,

 

Although this is a good workaround for the rolling months, this is not the best approach for this.

 

Check the blog below where you have an example of selecting the month and the N months you want to have displayed on your charts and this works with filter of dates.

 

http://sqljason.com/2018/03/display-last-n-months-selected-month-using-single-date-dimension-in-powe...

 

Regards,

MFelix

 



Did I answer your question? Mark my post as a solution!

Proud to be a Datanaut!




Re: Dynamic Relative Date Filtering

Hi @MFelix, this is perfect thanks!

 

I suppose my next issue which I didn't mention is that the calculation I'm looking to roll back 13 months is a YTD calculation. So it only starts from this year. Think I need to amend my DAX calculation but that has certainly helped my issue

Helpful resources

Announcements
GregDeckler

How to Get Your Question Answered Quickly

Power BI Super User, Greg Deckler, explains

Summit North America

Power Platform Summit North America

Register by September 5 to save $200

Back to School Contest

Back to School Contest

Engage and empower students with Power BI!

MBAS Gallery

Watch Sessions On Demand!

Continue your learning in our online communities.

Top Ideas
Users Online
Currently online: 44 members 1,101 guests
Please welcome our newest community members: