Once you calculate your weekly numbers, calculating week over week numbers becomes very simple when using the Lag or Lead functions. Lag and Lead are Window Functions that shifts all of the rows in a column. The key to using Lag and Lead properly is to ensure that your data is sorted in a unique order, with no “ties” in the sort order. Using Multi-Column Sort Criteria is the best way to ensure that.
Calculate Week over Week numbers using Lag
-
Group you data by week.
-
Add a column on the same level as your grouped week column. You can call this "Weekly Sales", enter the formula Sum([Sales Amount]).
-
Sort weekly sales data by date in Ascending order, ensuring a unique sort order.
- Once you have your data sorted, create a new column and enter in [Weekly Sales]/ Lag ([Weekly Sales])
- Change the column format to percent.
Calculate Week over Week numbers using Lead
-
Group you data by week.
-
Add a column on the same level as your grouped week column. You can call this "Weekly Sales", enter the formula Sum([Sales Amount]).
- Sort weekly sales data by date in Descending order, ensuring a unique sort order.
- Once you have your data sorted, create a new column and enter in [Weekly Sales]/ Lead ([Weekly Sales])
- Change the column format to percent.