cancel
Showing results for
Did you mean:

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

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

Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

Power BI Monthly Update - August 2024

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

Fabric Community Update - August 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors
Top Kudoed Authors