measure to dynamically find previous year

[jason5703]

I'm trying to create a measure to find the ranking of the previous year from the year selected by one of my slicers.  Would be great if this was dynamic as the data is going to be a rolling five years however if i can't that's not a huge issue.  I have this currently but I don't think it is getting the distinct rank and is adding up the ranks for the specific think over all the years.

Last Year Rank =

CALCULATE(

SUM(scorecard[RANKING]),

IF(

scorecard[TIMEFRAME_RANGE] = "2019","2018",IF(scorecard[TIMEFRAME_RANGE] = "2018","2017",IF(scorecard[TIMEFRAME_RANGE]="2017","2016",IF(scorecard[TIMEFRAME_RANGE]="2016","2015",IF(scorecard[TIMEFRAME_RANGE]="2015","2014",""))))))

This works correctly to give me last years rank:

Last Year Rank1 =

CALCULATE(

SUM(scorecard[RANKING]),

scorecard[TIMEFRAME_RANGE] = "2017")

[amitchandak]

Have you tried sampleperiodlastyear https://docs.microsoft.com/en-us/dax/sameperiodlastyear-function-dax

[jason5703]

 I tried this but it doesn't work (function previousyear has been used in a true/false expression that is used as a table filter expression.  this is not allowed.)

Last Year Rank =

CALCULATE(

SUM(scorecard[RANKING]),

scorecard[TIMEFRAME_RANGE] = (PREVIOUSYEAR(scorecard[TIMEFRAME_RANGE])))

[SteveCampbell · Accepted solution]

From the look of your code, your TIMERANGE is not a date column. Change it to whole number then try:

Last Year Rank =
   var _SelectedYear = selectedvalue(scorecard[TIMEFRAME_RANGE])
RETURN
CALCULATE(
   SUM(scorecard[RANKING]),
   scorecard[TIMEFRAME_RANGE] = _SelectedYear -1 )

Appreciate your Kudos
Connect with me!

Stay up to date on  
Read my blogs on  

[jason5703]

Thanks Steve, looks like I just need to correct the format as the timeframe_range column is text, not numeric.  

[SteveCampbell]

Last Year Rank =
   var _SelectedYear = selectedvalue(scorecard[TIMEFRAME_RANGE])
RETURN
CALCULATE(
   SUM(scorecard[RANKING]),
   scorecard[TIMEFRAME_RANGE] = _SelectedYear -1 )

Appreciate your Kudos
Connect with me!

Stay up to date on  
Read my blogs on