Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
8 years ago
Solved

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...
Need Help
  • mattbrice's avatar
    mattbrice
    8 years ago

    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.

Anonymous's avatar
Anonymous
Not applicable
8 years ago

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's avatar
mattbrice
Icon for Solution Sage rankSolution Sage
8 years ago

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" )
    )