cancel
Showing results for
Did you mean:

The ultimate Microsoft Fabric, Power BI, Azure AI & SQL learning event! Join us in Las Vegas from March 26-28, 2024. Use code MSCUST for a \$100 discount. Register Now

Frequent Visitor

## Compare current year sale vs last years sale groupby store name

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=...

1 ACCEPTED SOLUTION
Super User

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

4 REPLIES 4
Frequent Visitor

@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.

Frequent Visitor

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

Frequent Visitor

@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".

Super User

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

Announcements

#### Microsoft Fabric Community Conference

Join us at our first-ever Microsoft Fabric Community Conference, March 26-28, 2024 in Las Vegas with 100+ sessions by community experts and Microsoft engineering.

#### Power BI Monthly Update - February 2024

Check out the February 2024 Power BI update to learn about new features.

#### Microsoft Fabric Career Hub

Explore career paths and learn resources in Fabric.

#### Microsoft Fabric Partner Community

Engage with the Fabric engineering team, hear of product updates, business opportunities, and resources in the Fabric Partner Community.

Top Solution Authors
Top Kudoed Authors