Dynamically display last 12 months in chart visual based on Date Slicer which is in Hirearchy model

[Tu-Learn]

I would like to know if there is a way to display dynamically last 12 months data based on Year/Quarter/Month Selection

Tried Many ways but couldnt achieve with Date Hirearchy filter.

If user selects 2021 Q1 ..It should display last 12 months data with axis 2021.01 , 2021.02 , 2021.03 , 2020.12 , 2020.11 , 2020.10 etc

if user selects 2021 March ..It should display march data along with last 12 months data.

Any help would be appreciated 🙂

[Chrisjr]

It was useful for me.
I thought about sharing this tutorial for anyone who would have the same issue but with other visuals where you don't want to activate the relationships between the two calendar tables in every single mesures and keep your dates fields from your main calendar table. 

Show last 6 months based on user single slicer selection from SQLBI 

[Tu-Learn]

Hi @ValtteriN  

I followed the same process as you said but the visual displaying whatever month i selected instead of last 12 months, not sure where i missed .

[ValtteriN]

Hi,

Is your relationship M:1 and inactive with filter direction "single" like in my example? Another possible problem might be with the slicer visual. Are you using 'Calendar for visual' table as the slicer? At a glance your DAX seems to follow the same logic so the problem is with visual or relationships.

[ValtteriN · Accepted solution]

Hi,

There are multiple ways to do this. One of the more straightforward ways is to make a copy of your calendar table:

Calendar for Visual = 'Calendar'

Then create following relationship:

Now create measure with following dax:

Last12MonthsFilter =

VAR NumOfMonths = 12

VAR ReferenceDate = MAX ( 'Calendar'[Date] )

VAR PreviousDates =

DATESINPERIOD (

'Calendar For visual'[Date],

ReferenceDate,

NumOfMonths,

MONTH

)

VAR CurVisCal =

MAX ( 'Calendar for Visual'[Date] )

RETURN

calculate(IF (

CurVisCal

in PreviousDates,

1,

0)

,USERELATIONSHIP('Calendar for Visual'[Date],'Calendar'[Date]))


Then apply that as a filter to your visual. 

End result:
The visual uses calendar[Date] and slicer calendar_for_visual[date]

I hope this helps and if it does consider accepting this as a solution and giving the post a thumbs up!