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
jason5703
Helper I
Helper I

measure to dynamically find previous year

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")
1 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  linkedin-logo.png
Read my blogs on  powerbi.tips_.png



Did I answer your question? Mark my post as a solution! Proud to be a Super User!


Connect with me!
Stay up to date on  
Read my blogs on  



View solution in original post

5 REPLIES 5
amitchandak
Super User
Super User

 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])))

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  linkedin-logo.png
Read my blogs on  powerbi.tips_.png



Did I answer your question? Mark my post as a solution! Proud to be a Super User!


Connect with me!
Stay up to date on  
Read my blogs on  



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

SteveCampbell
Memorable Member
Memorable Member

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  linkedin-logo.png
Read my blogs on  powerbi.tips_.png



Did I answer your question? Mark my post as a solution! Proud to be a Super User!


Connect with me!
Stay up to date on  
Read my blogs on  



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.