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!
