Forum Discussion
Inventory On Hand Quantity-DAX
Hello, I am trying to calculate Quantity on Hand on my moving Inventory over a period. Basically, for example, in the table below, I have my Quanitites for a plant and material over the period of...
Interesting problem. What I did was add a column for month name since you didn't mention having a calendar table, then put Months on the rows then wrote this measure:
Final Inventory:=SUMX ( FILTER ( ADDCOLUMNS ( SUMMARIZE ( Table1, Table1[Month],Table1[Date],Table1[Material],Table1[Plant] ), "Max_Date", CALCULATE ( MAX ( Table1[Date] ), ALLEXCEPT ( Table1, Table1[Material], Table1[Plant], Table1[Month] ) ), "avg Qty", CALCULATE ( AVERAGE ( Table1[Qty] ) ) ), [Max_Date] = Table1[Date] ), [avg Qty] )Altought this assumed your example results for March wasn't accurate(?) I computed 500.
mattbrice
Solution Sage
Solution SageInteresting problem. What I did was add a column for month name since you didn't mention having a calendar table, then put Months on the rows then wrote this measure:
Final Inventory:=SUMX (
FILTER (
ADDCOLUMNS (
SUMMARIZE (
Table1,
Table1[Month],Table1[Date],Table1[Material],Table1[Plant]
),
"Max_Date", CALCULATE (
MAX ( Table1[Date] ),
ALLEXCEPT ( Table1, Table1[Material], Table1[Plant], Table1[Month] )
),
"avg Qty", CALCULATE ( AVERAGE ( Table1[Qty] ) )
),
[Max_Date] = Table1[Date]
),
[avg Qty]
)Altought this assumed your example results for March wasn't accurate(?) I computed 500.
That's excatly what I need.
I only highlighted February as an example, so din't calculate for March(which should be 500). This was a great solution! Definitely taught me a couple of new ways to think.
Thanks a lot!
Atul