Forum Discussion
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.
Hi Anonymous,
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.
Regards,
MFelix
2 Replies
Hi Anonymous,
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.
Regards,
MFelix
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