Dynamic Relative Date Filtering

[robstewart89]

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.

[MFelix · Accepted solution]

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

[robstewart89]

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