Formula for calculating percent change in sales week over week

Support received a question on how to calculate percent change in sales week over week. As this comes up fairly often, I wanted to share how you can do this!

Here we have a table of weekly sales data.

The formula in the +/- Last Week column is:

``````last_week_date = DATEADD(\$Date, weeks=-1)
last_week_sales = Daily_Sales.lookupOne(Date=last_week_date).Sales
(\$Sales - last_week_sales)/last_week_sales if last_week_sales else 0
``````

Let’s break this down line by line.

`DATEADD(\$Date, weeks=-1)`: We use DATEADD() to find the date that is one week prior to this record’s date. Looking at row 1, one week prior to 05/10/2023 would be 05/03/2023. We assign this date to the variable `last_week_date`.

`Daily_Sales.lookupOne(Date=last_week_date).Sales`: We use a lookupOne formula to find the record in the Daily Sales table where the value in the Date column matches the value found for `last_week_date`. We then use dot notation to pull the `Sales` value for that record. For the record with date 05/03/2023, the sales would be 1200. We assign this value to the variable `last_week_sales`

`(\$Sales - last_week_sales)/last_week_sales`: Finally, we do the actual calculation to compare sales.

`if last_week_sales else 0` gets rid of the error for the oldest record where no prior week exists.

This formula returns the % as a decimal so be sure to change the column type to Numeric and select %

3 Likes