cancel
Showing results 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

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

4 REPLIES 4
Community Support

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

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

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.

Frequent Visitor

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!

Frequent Visitor

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!

Super User

@laughingchicken , refer if my blog or video can help

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