Rank by Current Year Using Multiple Dimension and Multiple Years

[Dalla_Terra]

I am hoping someone can help me out with this. I feel like I am close but just not getting the results I need. 

I need to create a display that ranks top brands by sales but I want to see how the performance was over the past 3 years and base the ranking off performance of the most current year. 

I have tried a number of ways to do this but each time the rank comes from the earliest year in the series rather than the most current. 

The expression I have written for the measure is: 

Brand Rank 9L =
     Var BrandRank = RANKX(

          ALL(Brand[Brand Name]),[Sales 9L],,DESC)
RETURN
BrandRank

The [Sales 9L] field is a measure that is calculated as:

Sales 9L = Sumx(Values(Dates[Year]),TOTALYTD(sum(Depletion[Sales 9L]),Dates[Date]))

I am sure it is just a matter of inserting a filter to say rank by LASTDATE or MAX but I can't seem to get the syntax right. 

Any help would be greatly appreciated. 

[amitchandak]

@Dalla_Terra , Not very clear on expected output. Can you share that.

As of now, your current formula will calculate Rank for each year. So not sure what you mean by driven by last year. Do you need the top 10 brands of the current year as filter?

This will give continuous rank

Brand Rank 9L =
     Var BrandRank = RANKX(

          ALL(Brand[Brand Name], Date[Year]),[Sales 9L],,DESC)
RETURN
BrandRank

Sales 9l  = sum(Depletion[Sales 9L])

[Dalla_Terra]

Thanks for your reply, yes, I am trying to display the rank by the most current year but also show the performance for the years prioir as well.

Let me try to clarify. 

I am using this calculation to create a bar graph. I would like to display a ranking of the top 10 products on a page with the performance of each product by year but would like to have them sorted so that the most current year (2021) is the sort order. 

In my graph below, green represents the current year (2021) but as you can see the first product ranks lower than the second in the year 2021. There are others products that are out of order as well. 

The table I shared shows what the output is currently and the graph below shows what that looks like visually. 

I can't seem to find a way to sort the ranking by the most current year.