Week over Week

[Anonymous]

Hi all, 

I need to calculate week over week change. The data in my table is raw (not aggregated) so I have many sales per week.

Example: 1$ in store #1 for week 1, 5$ in store #2 for week 1etc. Basically each row is a different store and/or different week.

I also have a column for week sequence, 1 being the most recent week and 26 being the oldest week (I dont have a proper week date). Not sure how to calculate week over week change since the sales are not aggregated (I dont have a "measure" summed up) and how to deal with the week sequence.

Columns: Sales, Week_Seq, Store (as previously mentioned, data is not aggregated in the table so there is many rows for the same store and many rows for the same week).

Any ideas? Thanks!

[DataZoe · Accepted solution]

@Anonymous Here are some tips that may help:

You can create a measure to aggregate your sales, such as

Sales = SUM(Table[Sales])

and then you can figure out the prior week using the Week_Seq:

Prior Week = 

VAR ThisWeek = max(Table[Week_Seq])

VAR LastWeek = ThisWeek - 1

RETURN

CALCULATE([Sales],Table[Week_Seq] = LastWeek)

And putting it together you to see Week Over Week Change:

Week Over Week Change = 

DIVIDE([Sales]-[Prior Week],[Prior Week])

or 

Week Over Week Change = 

VAR ThisWeek = max(Table[Week_Seq])

VAR LastWeek = ThisWeek - 1

VAR ThisWeekSales = CALCULATE([Sales],Table[Week_Seq] = ThisWeek)

VAR LastWeekSales = CALCULATE([Sales],Table[Week_Seq] = LastWeek)

RETURN

DIVIDE(ThisWeekSales - LastWeekSales,LastWeekSales)

I can provide more specific guidence if you provide some of the data in a table too, as I am not sure how you are handling year over year in this example either.

[Anonymous]

it worked well, thanks a lot!

[amitchandak]

@Anonymous , if you have a week year, we can get a date, but as long as the week sequence is correct that will work.

Create a new table with week sequence and year , if available (add year week , if there), Let say table name is Date

Option column if sequence is not continuous

OR
Week Rank = RANKX(all('Date'),'Date'[Week Seq],,ASC,Dense) //YYYYWW format
measures  //week rank or Week Seq
This Week = CALCULATE(sum('order'[Qty]), FILTER(ALL('Date'),'Date'[Week Seq]=max('Date'[Week Seq])))
Last Week = CALCULATE(sum('order'[Qty]), FILTER(ALL('Date'),'Date'[Week Seq]=max('Date'[Week Seq])-1))

refer if needed

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=pnAesWxYgJ8