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 time. When I report my ending inventory (or Qty on hand) for a month, I want the last non blank value for a Plant-Material grain.

 

So, for February, I want my QTY on hand to be 260. i.e., lastnonblank value for a Plant-Material grain (as shown in different colors below). But, I also want the duplicate rows to be averaged -->>for  2/6/2017 there are two records so I average them as (120+120)/2)

 

 



I am using the DAX formular: 

=CALCULATE(
AVERAGE(Table1[Qty]),
VALUES(Table1[Material]),
VALUES(Table1[Plant]),
VALUES('Calendar'[Date]),
LASTNONBLANK(
'Calendar'[Date],
CALCULATE(
Sum(Table1[Qty])
)))

 

Which gives me this result below:

 

 

If you can see it's giving me the last value for february. 

 

Could anyone please help me fixing this DAX?

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.

4 Replies

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