Forum Discussion

pa2021's avatar
pa2021
Frequent Visitor
5 years ago

How to have Initial Inventory (Earliest Date Values)+Production-Sales=Remaining Inv. (Matrix Table)

Hello, How would I go about having a dax measure, giving me:   Initial Inventory, based on the earliest date found in the date column; + (Sum of Production) - Sum of Sales = Remaining inventory...
Need Help
Show and Tell
pa2021's avatar
pa2021
Frequent Visitor
5 years ago

Hello amitchandak !
For some reason, I'm getting the error below:

amitchandak's avatar
amitchandak
Super User
5 years ago

pa2021 , my mistake, try like 

 

calculate(sum(Table[intial Inventory]), filter(Table, Table[Date] = calculate(min(Table[date]), allexcept(Table, Table[product]))))
-  CALCULATE(SUM(Table[Production Qty]),filter(allselected(Table),Table[Date] <=max(Table[Date])))
+  CALCULATE(SUM(Table[Sales Qty]),filter(allselected(Table),Table[Date] <=max(Sales[Date])))

  • pa2021's avatar
    pa2021
    Frequent Visitor
    5 years ago

    Hi again amitchandak ,

    Not certain if this is too much to ask, but here's the output:

     

    The findings:

    It's summing initial inventory with itself, giving a doubled result.

    It doesn't show Production, Sales and Remaining Inventory for each day. Instead, it gives a total.

    Would it be possible to have these 3 columns for each day, besides Initial Inventory, just like to goal below?