topic Re: Dynamically display last 12 months in chart visual based on Date Slicer which is in Hirearchy model in DAX Commands and Tips

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

Tu-Learn — Sat, 08 Jan 2022 19:41:23 GMT

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 🙂

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

ValtteriN — Sat, 08 Jan 2022 20:36:21 GMT

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,

,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!

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

Tu-Learn — Sat, 08 Jan 2022 21:23:08 GMT

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 .

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

ValtteriN — Sat, 08 Jan 2022 23:57:24 GMT

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.

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

Chrisjr — Mon, 20 Feb 2023 13:35:18 GMT

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

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

kb83 — Tue, 20 Feb 2024 13:28:20 GMT

This very interesting solution. However I would like to modify it a bit and maybe it will be easy for you support me. I follow you guidance and everything works perfect but I would like my Last12Month works only when no "Date" filter/slicer is applied. If user select on the slicer specific month or year ect. then the measure should show exact selected period (with no additional previous 12 months data). Is there a way that we can doi it?

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

kb83 — Tue, 20 Feb 2024 23:44:54 GMT

Hi,
Thank you for your reply. Actually I want my trend chart shows last 12 months as default chart view but if any Date filter is selected then the chart/measure should "reset" this 12 months period from the chart view and behave like a standard view that reacts on the slicer selection i.e if user selects "January" it should show January only and not January with previous 12 months...Hope this explanaition is clear now.

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

Bwendos_Den — Thu, 21 Mar 2024 08:42:12 GMT

Hi ValtteriN

Do you have a solution where I do not need to create a seconde calendar table?