Forum Discussion
Week over Week
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!
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.
3 Replies
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=pnAesWxYgJ8Anonymous 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.
