Forum Discussion

unusdusrname's avatar
unusdusrname
Frequent Visitor
3 years ago

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 t...
Need Help
amitchandak's avatar
amitchandak
Super User
3 years ago

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-Power-BI-Turning/ba-p/1187482
https://medium.com/chandakamit/cheat-sheet-any-weekdays-week-start-date-just-one-variable-apart-6b2e6f593958

 

unusdusrname's avatar
unusdusrname
Frequent Visitor
3 years ago

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?