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.
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!
Hi @laughingchicken ,
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.
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!
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!
@laughingchicken , 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-La...
https://www.youtube.com/watch?v=pnAesWxYgJ8
Time Intelligence, DATESMTD, DATESQTD, DATESYTD, Week On Week, Week Till Date, Custom Period on Period,
Custom Period till date: https://youtu.be/aU2aKbnHuWs&t=145s
You 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 format
and 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])))
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
54 | |
25 | |
23 | |
15 | |
11 |
User | Count |
---|---|
77 | |
61 | |
47 | |
18 | |
12 |