How to filter "In the last n" months

[HB13]

Hi all 🙂 

I need to filter an amount within the last 12, 24 and 36 months from the latest date my date filter is set to.

My date filter is currently set to 01/01/2022 - 30/04/2022, so the visual should filter the amount between these dates;

12 months: 01/05/2021 - 30/04/2022

24 months: 01/05/2020 - 30/04/2022

36 months: 01/05/2019 - 30/04/2022

How is it possible to achieve this? None of the Relative Date settings on the filter are suitable.

Please advise!

Many many thanks

[amitchandak · Accepted solution]

@HB13 , If you do not need to show date related column on the axis you can try like

Rolling 12 Sales =
var _max = maxx(allselcted(date),date[date]) // or today()
var _min = date(year(_max), month(_max)-12,1)
return
CALCULATE(SUM(Sales[Sales Amount]),filter(all(date), date[date] <=_max && date[date] >=_min))

or

Rolling 12 = CALCULATE(sum(Sales[Sales Amount]),DATESINPERIOD('Date'[Date ],MAX('Date'[Date ]),-12,MONTH))

But if select 1 month and want to show 12 month's trend then the slicer should be on an independent date table

//Date1 is independent Date table, Date is joined with Table
new measure =
var _max = maxx(allselected(Date1),Date1[Date])
var _min = eomonth(_max, -12) +1
return
calculate( sum(Table[Value]), filter('Date', 'Date'[Date] >=_min && 'Date'[Date] <=_max))

Need of an Independent Date Table:https://www.youtube.com/watch?v=44fGGmg9fHI