Forum Discussion
Inventory On Hand Quantity-DAX
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.
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.
Testing a little bit more, I noticed that the "Grand Total" is adding up values in all the months. But we would want to show only the value of the last month, in this case month of March.
So the Grand total should be : 500 and not 800.
- mattbrice
You can add another measure like so and use it in the visual:
Final Inventory with Grand Total:=VAR lastnonblankmonth = LASTNONBLANK ( Table1[Date], [Final Inventory] ) RETURN CALCULATE ( [Final Inventory], Table1[Month] = FORMAT ( lastnonblankmonth, "MMMM" ) )