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

[pa2021]

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.

Here's a snapshot of the goal:

Here's a snapshot of the way it is right now:

Any help is appreciated!

Antonio

[amitchandak]

@pa2021 , Try a measure like

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

[pa2021]

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

[amitchandak]

@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]

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?

[amitchandak]

@pa2021 Can you share sample data and sample output in table format? Or a sample pbix after removing sensitive data.

[pa2021]

Hello @amitchandak ,

Here's a test version: https://drive.google.com/file/d/156jqQf88WR2m48xPZawyKPuG-2RQDDM0/view?usp=sharing

Appreciate your time/help.

[pa2021]

Here it is, @amitchandak !

Link to download the file:

https://drive.google.com/file/d/1YE2mSPGgvG2Dhyi4alzTbWyNVTPTXvp9/view?usp=sharing
Appreciate your time!