Compare current year sale vs last years sale groupby store name

[williamcheng]

Hello,

I try to created a new Measure that can comapre current years sale ranking vs last year sale ranking change. i did a part that rank all store sale by year,  in additional to that, i also want to see the sale ranking change by compare to previous year or previous quarter. like Apple current year ranking #1 but last year ranking in #3, the Ranking Change column current year should be +2.  any help would be appreciate it. 

https://www.dropbox.com/s/undehlrvn56ikq5/Ranking%20Current%20Year%20VS%20Previous%20Years..pbix?dl=...

[williamcheng]

@amitchandak,  The above formula works great. I got another scenario if that make sense. 

When i slice the Timeline for Current Time Period. is it possible for last years data remain whole Years without getting change as i slice the Timeline. so no matter how many months i pick for current years, last year always remian 12 months data ( Ex:  from 1/1/2020 to 12/31/2020 no change ).  appreciate with any help. 

[williamcheng]

@amitchandak , Thank you so much. I just figured it out. It works.  I really appreciate it.

[williamcheng]

@amitchandak, Thank you for the help, i did created a Meaure per your message, some how, it does not return what i expect,  please see below,  the last column "Rank Diff" does not reflect the compare ranking. thanks in advance.

the result i expecting to are showing below on the right.  for example, Walmart in 2021 rank#1, in 2020 rank #3 , so the Rank Different should be "+2".  

[amitchandak · Accepted solution]

@williamcheng , Create measure with help from date table and time intelligence

YTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESYTD('Date'[Date],"12/31"))
Last YTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESYTD(dateadd('Date'[Date],-1,Year),"12/31"))

Then rank

Rank TY = rankx(all(Table[Stote]), [YTD Sales])

Rank LY = rankx(all(Table[Stote]), [Last YTD Sales])

Diff= [Rank TY] - [Rank LY]