Forum Discussion
Week-over-week change $ and %
I have data that captues a "snap shot" of a price each week, example (below).
How can I measure week over week change? As a % and $ - I can do some visuals for the trending/direction, but need the ability to capture the detlas in a calculation.
A possible alternative is to transform this into monthly data, and then compare months, but I would have to average the costs some (MODE).
| DATE | ITEM | COST |
| 20/03/2022 00:00:00 | 123456 | 2.81 |
| 27/03/2022 00:00:00 | 123456 | 2.81 |
| 03/04/2022 00:00:00 | 123456 | 2.57 |
| 10/04/2022 00:00:00 | 123456 | 2.57 |
| 17/04/2022 00:00:00 | 123456 | 2.57 |
| 24/04/2022 00:00:00 | 123456 | 2.57 |
| 01/05/2022 00:00:00 | 123456 | 2.35 |
| 08/05/2022 00:00:00 | 123456 | 2.25 |
| 15/05/2022 00:00:00 | 123456 | 2.77 |
| 22/05/2022 00:00:00 | 123456 | 2.71 |
| 29/05/2022 00:00:00 | 123456 | 2.71 |
| 05/06/2022 00:00:00 | 123456 | 2.71 |
| 12/06/2022 00:00:00 | 123456 | 2.75 |
| 19/06/2022 00:00:00 | 123456 | 3.08 |
| 26/06/2022 00:00:00 | 123456 | 3.08 |
| 03/07/2022 00:00:00 | 123456 | 3.08 |
3 Replies
reate a new column for the week-over-week change in cost by using the DIFFERENCE function and setting the second argument to 1 (to indicate a 1-week difference). Syntax : =DIFFERENCE(SUMMARIZE(FILTER(YOUR_TABLE,YOUR_TABLE[DATE] = MAX(YOUR_TABLE[DATE])),YOUR_TABLE[COST]),SUMMARIZE(FILTER(YOUR_TABLE,YOUR_TABLE[DATE] = MAX(YOUR_TABLE[DATE])-1),YOUR_TABLE[COST]),1)
Create a new column for the percentage change by dividing the difference column by the previous week's cost and multiplying by 100. Syntax : =[WEEK OVER WEEK CHANGE] / SUMMARIZE(FILTER(YOUR_TABLE,YOUR_TABLE[DATE] = MAX(YOUR_TABLE[DATE])-1),YOUR_TABLE[COST]) * 100
Create a new column for the dollar change by subtracting the previous week's cost from the current week's cost. Syntax : =SUMMARIZE(FILTER(YOUR_TABLE,YOUR_TABLE[DATE] = MAX(YOUR_TABLE[DATE])),YOUR_TABLE[COST]) - SUMMARIZE(FILTER(YOUR_TABLE,YOUR_TABLE[DATE] = MAX(YOUR_TABLE[DATE])-1),YOUR_TABLE[COST])
Hi - for some reason DIFFERENCE is not a valid function for me, not sure why.
you may add a column like:
CostLastWeek=VAR _weeknum = WEEKNUM([Date])RETURNMINX(FILTER(TableName,TableName[Date]=_weeknum-1),TableName[Cost])
