Forum Discussion

bvbull200's avatar
bvbull200
Helper III
2 years ago
Solved

Counting the Number of Changes Between Two Datapoints (Variable Number of Datapoints per Record)

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 #DateValue
100112/01/2023  

10

100112/08/2023  5
100112/15/2023  12
100112/22/2023  3
100212/15/2023  10
100212/22/2023  0
100312/01/2023  25
100312/15/2023  25
100312/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!

2 Replies

    • bvbull200's avatar
      bvbull200
      Helper III

      Attempting this now.  The Previous Value of Part # column has been attempting to generate for over 20 minutes, so what I am looking for might not be realistic for the size of my dataset.  I will keep letting it run for the time being and see what I get.  

       

      I appreciate the help.