Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Tu-Learn
New Member

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

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 🙂

 

TuLearn_0-1641670509286.png

 

1 ACCEPTED SOLUTION
ValtteriN
Super User
Super User

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:
ValtteriN_0-1641674032433.png


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:
ValtteriN_1-1641674128588.png

 

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!




Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




View solution in original post

7 REPLIES 7
Chrisjr
Helper IV
Helper IV

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
New Member

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 .

 

TuLearn_0-1641676835443.png

 

TuLearn_1-1641676888075.png

 

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.





Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




ValtteriN
Super User
Super User

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:
ValtteriN_0-1641674032433.png


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:
ValtteriN_1-1641674128588.png

 

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!




Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




Hi @ValtteriN 

 

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

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.

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?

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors