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.

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

    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

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