Reply
Member
Posts: 46
Registered: ‎04-06-2017

Re: Show the last X years based on the current Selection of the Year

 Now working fine, I gone through post again and done some changes in formulas and its working fine for Line chart as well.

When I selected Year 2011 from slicer it shows me that year as well as Previous 6 years Sales under Line chart

 

Thanks @alexanderg and @Greg_Deckler Really nice Post its help me and poeple who are new in Power BI and in DAX.

 

Working for 6 years for based on current selection.JPG

 

 

 

 

  

 

 

Highlighted
Super User
Posts: 9,932
Registered: ‎07-11-2015

Re: Show the last X years based on the current Selection of the Year

Awesome, always glad to help out! Glad you got it working.


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

Proud to be a Datanaut!


Member
Posts: 46
Registered: ‎04-06-2017

Re: Show the last X years based on the current Selection of the Year

@Greg_Deckler 

 

Hello

 

Same Requirtment i was trying for my main database where i need to Implement this

 

I have prepared years tables separately and trying to genrate Same Scenario But when i clicked and selected any year from slicer

not displaying Current +Previous 6 years data. please check below image

 

When i clicked on date 18 April 2016 from Slicer  its only show value 1 against for that year only But

I have prepared below formulas for this database

 

Measure = IF(MAX('Global'[weekno])>(MAX(WeekNo[weekno])-6) && MAX('Global'[weekno])<=MAX(WeekNo[weekno]),1,0)

 

This Week for last 6 Years = CALCULATE(SUM('Global'[ThisWeek]),FILTER('Global','Global'[weekno]>(MAX([weekno])-6)))

 

here in years tables i have data like

 

e.g 02-03-2017 in this format

 

 

Dynamic slicer.JPG

 

 correct me if i am doing any wrong in above.

 

 

Member
Posts: 46
Registered: ‎04-06-2017

Re: Show the last X years based on the current Selection of the Year

@Greg_Deckler

 

please check below images

 

when i selected date from slicer 11 April 2016 its show me  value 1 in Measure column  against this date only.

 

 

Value come for that year only.JPG

 

 

I used below formulas for that

 

Measure = IF(MAX('Global'[weekno])>(MAX(WeekNo[weekno])-6) && MAX('Global'[weekno])<=MAX(WeekNo[weekno]),1,0)

 

This Week for last 6 Years = CALCULATE(SUM([ThisWeek]),FILTER('Global','Global'[weekno]>(MAX([weekno])-6)))

 

But i want Result like below when i clicked on date 11 April 2016 its show me values for Previous date like

4 April 2016, 28 March 2016, 21 March 2016, 14 March 2016, 7 March 2017, 29 February 2016 for Previous 6 years .

 

Correct me if i doing wrong in formulas.

 

 

Frequent Visitor
Posts: 5
Registered: ‎06-04-2018

Re: Show the last X years based on the current Selection of the Year

Is there a way to evaluate a measure based on slicer selctions? For example I have a Year over Year card that i want to be evaluated based on the two years selected, so if someone clicks on "2018 and 2017" then it does those two years, but if i select "2017 and 2016" then it does those two years.