Hello everyone,
I have been stunk at one simple DAX for 2 days, I have got a data which includes the unit share achieved for certain week together with the sales quantity. I, then, backcalculated the Market Size by using [Sales Quantity] / [Unit Share].
Now I need to compare the weekly percentage different between those customised date table (not power BI normal one), however, the number is always calculated wrongly which I can't figure out why..
Looking at the below, the MKT is the current market based on the above calculation, the last row is the one that I am trying to get which never gets right. would it be kind if any of the experts can give me a hand on why this happens.. thank you so much on this 🙂
1 ACCEPTED SOLUTION
@Anonymous , Typically this what I do, I create a rank on yearweek or wek start date
Week Rank = RANKX(all("Date"),"Date"[Year Week],,ASC,Dense) //YYYYWW format
and then create measure like
measures
This Week = CALCULATE(sum("order"[Qty]), FILTER(ALL("Date"),"Date"[Week Rank]=max("Date"[Week Rank])))
Last Week = CALCULATE(sum("order"[Qty]), FILTER(ALL("Date"),"Date"[Week Rank]=max("Date"[Week Rank])-1))
Power BI — Week on Week and WTD
https://medium.com/@amitchandak.1978/power-bi-wtd-questions-time-intelligence-4-5-98c30fab69d3
https://community.powerbi.com/t5/Community-Blog/Week-Is-Not-So-Weak-WTD-Last-WTD-and-This-Week-vs-Last-Week/ba-p/1051123
https://www.youtube.com/watch?v=pnAesWxYgJ8
Hi Amit,
Thanks very much for your reply and your recommended method is very useful, the problem is I have also had a customised monthyear (i.e 202103 is March of 2021) which the day range is different to the normal calendar, for example, the normal calendar for Dec-2020 started from 1/12/20-31/12/20, whereas my customised calendar for Dec-20 started from 30/11/2020 to 3/1/2021 as it counts the full week's days for that month. so how should I replicate the week on week one to the month over month? thank you!
Hi @Anonymous,
Can you please share some dummy data that keep raw data structure with expected results? It should help us clarify your scenario and test to coding formula.
How to Get Your Question Answered Quickly
Regards,
Xiaoxin Sheng
@Anonymous , Typically this what I do, I create a rank on yearweek or wek start date
Week Rank = RANKX(all("Date"),"Date"[Year Week],,ASC,Dense) //YYYYWW format
and then create measure like
measures
This Week = CALCULATE(sum("order"[Qty]), FILTER(ALL("Date"),"Date"[Week Rank]=max("Date"[Week Rank])))
Last Week = CALCULATE(sum("order"[Qty]), FILTER(ALL("Date"),"Date"[Week Rank]=max("Date"[Week Rank])-1))
Power BI — Week on Week and WTD
https://medium.com/@amitchandak.1978/power-bi-wtd-questions-time-intelligence-4-5-98c30fab69d3
https://community.powerbi.com/t5/Community-Blog/Week-Is-Not-So-Weak-WTD-Last-WTD-and-This-Week-vs-Last-Week/ba-p/1051123
https://www.youtube.com/watch?v=pnAesWxYgJ8
