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

Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.

Reply
unusdusrname
Frequent Visitor

Calculate difference using a ranking column

Hello all. I have some cumulative sales data which publishes every week, and I need to figure out the delta in such a manner I can easily target the larger books/debooks. Lets say my data look like this:

GroupSubGroupContinentCountrySalesWeek endingOrder
FruitApplesAfricaZimbabwe10021/07/20233
FruitApplesAsiaFiji12014/07/20232
FruitOrangesAsiaVietnam3007/07/20231
FruitApplesAsiaFiji25021/07/20233
VegCarrotsAustralasiaNew Zealand1014/07/20232
VegCarrotsEuropeCzech Republic1507/07/20231

Subgroups are always subsets of groups, and countries are always subsets of continents. The week ending field is text and tends to get a bit freeform, so I'm generating the 'Order' column where oldest is smallest and the week ending:order relationship is 1:1. The sales number is cumulative of all prior weeks/'orders' in the dataset.

In this example we can see that in the last week we've sold 130 fijian apples, and it's this number I wish to calculate to display in a matrix that the user can drill into .

I previously asked a similar question and tried to modify that to suit my new question, but I'm missing a fundamental concept as while my totals are correct, the values at a continent/country level are preposterously large

 

Thanks 🙂

2 REPLIES 2
amitchandak
Super User
Super User

@unusdusrname , A column that can give you week sales from cumulative

 

sales this week=
var _date = maxx(filter( Table,[Group] = earlier([Group]) && [SubGroup] = earlier([SubGroup]) && [Continent] = earlier([Continent])
&& [Country] = earlier(Country) && [Week ending Order] < earlier([Week ending Order])),[Week ending Order])
return
[Sales] - Sumx(filter( Table,[Group] = earlier([Group]) && [SubGroup] = earlier([SubGroup]) && [Continent] = earlier([Continent])
&& [Country] = earlier(Country) && [Week ending Order] =_max ),[sales])

 

 

Measure to compare this week vs last week

 

Week Rank = RANKX('Date','Date'[Week Start date],,ASC,Dense)
OR
Week Rank = RANKX('Date','Date'[Year Week],,ASC,Dense) //YYYYWW format


These measures can help
This Week = CALCULATE(sum('Table'[Qty]), FILTER(ALL('Date'),'Date'[Week Rank]=max('Date'[Week Rank])))
Last Week = CALCULATE(sum('Table'[Qty]), FILTER(ALL('Date'),'Date'[Week Rank]=max('Date'[Week Rank])-1))

 

to get the week start/week end of Friday, refer

 

2.Any Weekday Week - Start From Any day of Week
https://community.powerbi.com/t5/Community-Blog/Any-Weekday-Week-Decoding-Date-and-Calendar-2-5-Powe...
https://medium.com/chandakamit/cheat-sheet-any-weekdays-week-start-date-just-one-variable-apart-6b2e...

 

Thanks Amit. I didn't quite get your code to work however, PowerBi reports syntax errors.

So I expunged the surplus carriage returns, and added the square braces around [Country]. I also changed [Week Ending Order] for just [Order] as those two were different fields.

I've still got a syntax error in the return clause where [Order] = _max - am I right in thinking _max is intended to be a variable that's been defined? So it maybe ought to be _date defined in the var clause above? Here's what I tried.

sales this week = 
var _date = maxx(filter( Table,[Group] = earlier([Group]) && [SubGroup] = earlier([SubGroup]) && [Continent] = earlier([Continent]) && [Country] = earlier([Country]) && [Order] < earlier([Order])),[Order])
return
[Sales] - Sumx(filter( Table,[Group] = earlier([Group]) && [SubGroup] = earlier([SubGroup]) && [Continent] = earlier([Continent])&& [Country] = earlier([Country]) && [Order] =_date ),[sales])

 Just gotta love how PowerBI won't share even which line holds the supposed syntax error. Can you see what is wrong?

Helpful resources

Announcements
Microsoft Fabric Learn Together

Microsoft Fabric Learn Together

Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City

PBI_APRIL_CAROUSEL1

Power BI Monthly Update - April 2024

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

April Fabric Community Update

Fabric Community Update - April 2024

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

Top Solution Authors