I am importing a SQL table that looks like the below. When there is data available for a given date, the inventory level is provided. Sometimes I have data for every possible date, but sometimes only certain dates have data available. What I'm needing to determine is the number of times that the inventory level changes from one datapoint to the next. This isn't a count of unique values, but comparing the 1st entry to the 2nd, then the 2nd entry to the 3rd. Ideally, I would return the amount of the change (i.e. 1st value minus 2nd value, 2nd value minus 3rd value, etc.) and then I could count all non-zero values.
I have this [Inventory_Level]:
| Part # | Date | Value |
| 1001 | 12/01/2023 | 10 |
| 1001 | 12/08/2023 | 5 |
| 1001 | 12/15/2023 | 12 |
| 1001 | 12/22/2023 | 3 |
| 1002 | 12/15/2023 | 10 |
| 1002 | 12/22/2023 | 0 |
| 1003 | 12/01/2023 | 25 |
| 1003 | 12/15/2023 | 25 |
| 1003 | 12/22/2023 | 10 |
What I'm hoping to accomplish is something that tells me for 1001 that there is a -5, +7, and a -9. Which I can then count as 3 changes and know which direction they went.
For 1002, there is just a -10.
For 1003, there is a 0 and a -15.
The dates will be on a rolling period, perhaps 10 weeks, but that isn't set in stone. I can't wrap my head around how to account for a different number of datapoints for each part. Hoping to solve this via a DAX measure, but open to solutions.
Thank you for any help!
