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

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
laughingchicken
Frequent Visitor

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:

WoW Rev OTB =
SUM ( 'Table'[sales on the book] )
- CALCULATE (
SUM ( 'Table'[sales on the book] ),
FILTER (
ALL ( 'table' ),
'table'[Year] = MAX ( 'table'[Year] )
&& 'table'[WeekNumber]
= MAX ('table'[WeekNumber]) - 1
&& 'table'[WeekDay] = MAX ( 'table'[WeekDay] )
)
)
 
The result turned very well, I got the total number for week over week sales. However, when I tried to apply on different region or destinations or choose other status, the week over week sales numbers showed all negative numbers which doesn't seem correct. 
laughingchicken_0-1659558642449.png(This is the screenshot for WoW sales without any filters)
laughingchicken_1-1659558704789.png

(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 4
v-jianboli-msft
Community Support
Community Support

Hi @laughingchicken ,

 

Based on your description, I have create a simple sample:

vjianbolimsft_0-1658486506046.png

 

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:

vjianbolimsft_1-1658486506048.png

 

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!

https://app.powerbi.com/links/20KZ7HmD__?ctid=39daea78-5875-4a88-ad41-9a8be32786f8&pbi_source=linkSh...

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! 

amitchandak
Super User
Super User

@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])))

Helpful resources

Announcements
July 2024 Power BI Update

Power BI Monthly Update - July 2024

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

July Newsletter

Fabric Community Update - July 2024

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