Creating a column that keeps track of price changes

[salemalnahdi14]

Hi, would appreciate some help in creating a column to keep track of changes in price and the date it happened.

For example, I have this dataset. The column counter is a live counter of the number of times a product is in the dataset and is sorted by date, so earliest date will start with 1 and continue from there:

Product ID	Date	Price	Product ID Counter
1	3/22/2022	50	1
1	6/27/2022	70	2
1	8/12/2022	100	3
2	1/17/2022	60	1
2	4/14//2022	40	2
3	2/11/2022	110	1
3	6/15//2022	150	2
3	10/18//2022	120	3

I would love my output to look like this, an extra column that tracks the changes in price to the previous entry. When a product is first introduced in the data then it will just be the price of the product.

Product ID	Date	Price	Product ID Counter	Price Changes
1	3/22/2022	50	1	50
1	6/27/2022	70	2	20
1	8/12/2022	100	3	30
2	1/17/2022	60	1	60
2	4/14//2022	40	2	(20)
3	2/11/2022	110	1	110
3	6/15//2022	150	2	40
3	10/18//2022	120	3	(30)

I created the product ID counter as I suspect it could be helpful to use in a formula, but not sure how to proceed. Was thinking of using the counter in a statement where:

• If Product ID Counter = 1, then Price
  
• If Product ID Counter>1,then Price - Price where Product ID Counter-1 

The second part of the formula is where I am not sure how to proceed as don't believe there is a "Where" function in the formula tool. Would love some help, thanks!

Also open to any other solutions!

[Luke_C · Accepted solution]

Hi @salemalnahdi14 

You can use a multi-row formula tool grouped by Product ID. No need for if statements:

[salemalnahdi14]

Thank you, this worked!

[Sarreddy]

@salemalnahdi14  My Solution for the requirement.