cancel
Showing results for 
Search instead for 
Did you mean: 
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

3 REPLIES 3
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!




Helpful resources

Announcements
Microsoft Build 768x460.png

Microsoft Build is May 24-26. Have you registered yet?

Come together to explore latest innovations in code and application development—and gain insights from experts from around the world.

May 23 2022 epsiode 5 without aka link.jpg

The Power BI Community Show

Welcome to the Power BI Community Show! Jeroen ter Heerdt talks about the importance of Data Modeling.

Power BI Dev Camp Session 22 with aka link 768x460.jpg

Check it out!

Mark your calendars and join us on Thursday, May 26 at 11a PDT for a great session with Ted Pattison!

charticulator_carousel_with_text (1).png

Charticulator Design Challenge

Put your data visualization and design skills to the test! This exciting challenge is happening now through May 31st!