Disclaimer: I just started working with DAX and I've read and watched a ton of content but I'm still getting hung up on very simple problems. I learn faster if I can see the solutions to the problems in my head.
I have an inventory table which holds all item specifics: Quantity on Hand, PAR, Location, etc.
I also have a transaction table that shows all of the (+/-) transactions that happened to any given item.
Instead of simply calculating a units in stock like:
UnitsInStockSimple =
CALCULATE (
SUM ( 'Transaction Data'[Qty] ),
FILTER (
ALL ( 'Date Table'[Date] ),
'Date Table'[Date] <= MAX( 'Date Table'[Date] )
)
)
I want to work in reverse and pick up the QOH field from the Inventory table and work backwards through the dates. The final result should be the QOH on for any given date:
Example for item 123
Current QOH is 400
Yesterday (during 1 day) there were 3 transactions in desc order: -3 (distribution), -1 (Adjustment), +6 (order receipt)
I would like to see the QOH for each date: 403, 404, 398
Because: If the last transaction subtracted 3 and today's QOH is 400, then the QOH before the transaction must have been 403.
