Week over Week sales calculation

[laughingchicken]

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. 

(This is the screenshot for WoW sales without any filters)

(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!

[v-jianboli-msft]

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.}

[laughingchicken]

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...

[laughingchicken]

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]

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