Skip to main content
cancel
Showing results for 
Search instead 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

Reply
williamcheng
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
amitchandak
Super User
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]

View solution in original post

4 REPLIES 4
williamcheng
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. 

 

williamcheng
Frequent Visitor

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

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

williamcheng_0-1638813756526.png

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

williamcheng_1-1638814939811.png

 

amitchandak
Super User
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]

Helpful resources

Announcements
Fabric Community Conference

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.

February 2024 Update Carousel

Power BI Monthly Update - February 2024

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

Fabric Career Hub

Microsoft Fabric Career Hub

Explore career paths and learn resources in Fabric.

Fabric Partner Community

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