Keeping Track of Price Changes

[salemalnahdi14]

Hi, would love 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 of to keep the price changes. When a product is first introduced in the data this the price change will 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]

This seems to be a duplicate post, refer to the solution posted at https://community.alteryx.com/t5/Alteryx-Designer-Discussions/Creating-a-column-that-keeps-track-of-...

[Felipe_Ribeir0]

Hi @salemalnahdi14 

One way of doing this