Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.

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
RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.

MayPowerBICarousel

Power BI Monthly Update - May 2024

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

LearnSurvey

Fabric certifications survey

Certification feedback opportunity for the community.

Top Solution Authors
Top Kudoed Authors