Forum Discussion
DAX Formula: Inventory QOH in reverse
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.
17 Replies
Hi Anonymous
I haven't tested this but have you tried reversing the operator?
UnitsInStockSimple = CALCULATE ( SUM ( 'Transaction Data'[Qty] ), FILTER ( ALL ( 'Date Table'[Date] ), 'Date Table'[Date] >= MAX( 'Date Table'[Date] ) ) )Thanks,
I will try that in the morning at work. The original problem with that formula is that it doesn't pull 'Inventory Data'[QOH] at all. The transactions are in: 'Transaction Data' [Qty].
Also, that formula only shows me the total per date, while the sum of all totals does accurately reflect the current QOH, that's not entirely what I want. I just want to be able to show a date and show the QOH for that date/item. (Maybe I'm displaying that measure incorrectly?)
Hi Anonymous,
Could you try the formula(untested) below to see if it works in your scenario? :smileyhappy:
Inventory QOH = VAR currentQOH = MAX ( 'Inventory Data'[QOH] ) VAR maxDate = CALCULATE ( MAX ( 'Date Table'[Date] ), ALL ( 'Date Table' ) ) VAR currentDate = MAX ( 'Date Table'[Date] ) RETURN currentQOH - CALCULATE ( SUM ( 'Transaction Data'[distribution] ) + SUM ( 'Transaction Data'[Adjustment] ) + SUM ( 'Transaction Data'[order receipt] ), FILTER ( ALL ( 'Date Table'[Date] ), 'Date Table'[Date] >= currentDate && 'Date Table'[Date] <= maxDate ) )Regards
Hi Anonymous had u found the solution? I have to built similar to count stock on hand quantity backwards based on FIFO receipts transactions. 1st table show SOH qty for the month, and 2nd table have receipts date and quantity for the material item at company level. How should I connect these two tables? Thanks for the team advise.
Hi,
Share some data, explain the question and show the expected result.
1st table : stock on hand position
Company Code Plant Material SOH Quantity Stock on Hand per Plant FIFO Quantity FR02 9060 100064013 1,349.000 852.000 220.421 FR02 9041 100064013 1,349.000 497.000 128.579 2nd table: receipt transactions with aging bracket
Company Code Plant Material Age Rcpt Date obs range & % Receipt Quantity FR02 9060 100064013 6 30-Nov-22 0 to 11 month 0 % 1000 FR02 9041 100064013 21 20-Aug-21 12 to 23 mths 25% 525 FR02 9041 100064013 51 30-Jul-21 > 24 months 50% 1990 FR02 9041 100064013 111 30-May-21 > 24 months 50% 2000 output expected: Obsolescene is Calculated at Company Code Level based on FIFO Aging Methodology. To compute SOH backward based on latest FIFO receipt tranactions that made up the ending SOH.
Source: 3. obs receipt aging extract (show applicable only) SOH Quantity 2 Last receipt date Receipt Qty Receipt ageing bracket 1000 Jun-21 1000 0% balancing figure 349 Aug-20 525 25% SOH Quantity : 1349
I need this for my project.
