Inventory On Hand Quantity-DAX

[Anonymous]

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?

[mattbrice · Accepted solution]

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]

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

[Anonymous]

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