Filter the visual to last 12 months based on the date range selected in the visuals slider

[Mahendran_C_S]

Hi,
I have a requirement to filter a visual to 12 months where that 12 months period ends at the end of date range sliced in the line chart visual axis.

The above screenshot is a visual which has the date as its x-axis where the zoom slider is enabled so that the user can select the date range. 

Also, I have a selection that has 12 Months and All. If the user clicks on 12 months the line chart filters the visual to 12 months .
for example,
The slider in the visual is between Aug,2019 to Oct,2021 and the 12 Months selection is selected. The visual should show data for Nov,2020 to Oct,2021 taking sliders last month as the 12th month. If All selection is selected the visual should show the data for date range in the slider i.e from Aug,2019 to Oct,2021 according to this example.
It would be great if anyone suggest any solution using DAX measures or some other ways to achieve this requirement.
Thanks in advance.

[amitchandak]

@Mahendran_C_S , if you select one date and want to show more range, then you need an independent date table

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

[Mahendran_C_S]

@amitchandak Thanks for your reply. It would be great if you elaborate in detail.

[amitchandak · Accepted solution]

@Mahendran_C_S , if you have a date table joined with your table and you select a date you can show rolling 12 data. but you can not show trends. This means last 12 months will group into one month only

To get trend for duration more than selected you need an independent date table, say date 1

and measure like below

//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))

explained the same in

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

the usual rolling 12 measures are

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