DAX FORMULA TO DECREMENT ROW BY ROW IN A COLUMN, BASED ON DATA FROM OTHER COLUMNS AND ROWS

[rderrickwhite]

I'm attempting to write a formula that decrements inventory in a column based on demand and qty available.  In the attached picture, column c is being decremented in column d, incrementally, based on the number of occurrences in column b.

[Cmcmahan · Accepted solution]

You need some sort of index or other sortable column that indicates the order of these events.  Otherwise, if the column isn't in this exact order (which is not guaranteed by the DAX engine), you'll get results that don't make sense.

Once you have that index set up, you can simplify this math down to subtracting a count of the rows from this table where the index/sorting column is less than the current index from the initial stock qty.

Also, are you setting this up as a measure, or a calculated column?  I would suggest changing your set up and creating this up as a measure, but here's the DAX for a calculated column, since that looks like what you're creating.

QTY_REMAINING = 'Table'[stock qty] - CALCULATE(COUNTROWS('Table'), FILTER(ALLEXCEPT('Table', 'Table'[unit], 'Table'[part needed]), 'Table'[Index] <= EARLIER('Table'[Index])) 

[rderrickwhite]

That worked perfectly.  Thanks so much!