Forum Discussion
Week over Week sales calculation
I am trying to figure out week over week calculation. I searched online and found DAX measure. I created columns for Year, WeekDay, and WeekNum. what I am using is like below:
(After I selected "Established" in status slicer, all the numbers turn into negative. Same thing would happen if I selected product market slicer.)
Can anyone help me how to apply week over week sales on categories? Thank you in advance!
4 Replies
Anonymous , refer if my blog or video can help
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=pnAesWxYgJ8Time Intelligence, DATESMTD, DATESQTD, DATESYTD, Week On Week, Week Till Date, Custom Period on Period,
Custom Period till date: https://youtu.be/aU2aKbnHuWs&t=145sYou need new column in date/week column
new columns
Week Start date = 'Date'[Date]+-1*WEEKDAY('Date'[Date],2)+1
Week End date = 'Date'[Date]+ 7-1*WEEKDAY('Date'[Date],2)
Week Rank = RANKX(all('Date'),'Date'[Week Start date],,ASC,Dense)
OR
Week Rank = RANKX(all('Date'),'Date'[Year Week],,ASC,Dense) //YYYYWW formatand measures
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))
Last year Week= CALCULATE(sum('Table'[Qty]), FILTER(ALL('Date'),'Date'[Week Rank]=(max('Date'[Week Rank]) -52)))
Last 8 weeks = CALCULATE(sum('Table'[Qty]), FILTER(ALL('Date'),'Date'[Week Rank]>=max('Date'[Week Rank])-8 && 'Date'[Week Rank]<=max('Date'[Week Rank])))WTD = CALCULATE(sum('Table'[Qty]), FILTER(ALL('Date'),'Date'[Week Rank]=max('Date'[Week Rank]) && 'Date'[WeekDay]<=max('Date'[WeekDay])-1))
LWTD = CALCULATE(sum('Table'[Qty]), FILTER(ALL('Date'),'Date'[Week Rank]=max('Date'[Week Rank])-1 && 'Date'[WeekDay]<=max('Date'[WeekDay])-1 ))This week = CALCULATE(sum('Table'[Qty]), FILTER(ALL('Date'),'Date'[Year]=max('Date'[Year]) && 'Date'[Week] = Max('Date'[Week]) ))
Last year same week = CALCULATE(sum('Table'[Qty]), FILTER(ALL('Date'),'Date'[Year]=max('Date'[Year])-1 && 'Date'[Week] = Max('Date'[Week])))Hi Anonymous ,
Based on your description, I have create a simple sample:
Please try:
WoW Rev OTB = SUMX ( ALL('Table'),[sales on the book] )- SUMX(FILTER(ALL('Table'),[Year]=MAXX(ALL('Table'),[Year])&&[WeekNumber]=MAXX(ALL('Table'),[WeekNumber])-1&&[WeekDay]=MAXX(ALL('Table'),[WeekDay])),[sales on the book])Output:
If the result is not what you want, please provide me with more details about your table and your problem or share me with your pbix file after removing sensitive data.
Refer to:
How to provide sample data in the Power BI Forum
How to Get Your Question Answered Quickly
Best Regards,
Jianbo Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Hey Jianbo,
Thank you very much for your solution! Unfortunately, I tried but numbers changed when I selected status or year. I uploaded my test file, this file is an example of database I have. The main purpose is to show week over week sales, and the number can reflect by choosing different status. Based on what we have so far, week over week sales result run perfectly if we don't make any filter. It makes me think of whether the current measure is based on column, but what I need here need to be calculated by row. Please let me know if you have any questions. Thank you in advance for your generous help!
I'm sorry, I have been trying to find out how to upload my test file to the chatbox. This is the link to access, I apologize for your inconvenience. Please let me know if you have any problem accessing the file. Thank you!
