inventory management - cumulative close balance

[BlueM]

Ciao friends,

I came into a "deadend"  ,

I'm attempting to build an rolling inventory balance, what iam looking for is 

to build a table, that display :

my componente number, my open balance, units of componente that needed for a production, and close balance 

( close balance calculate is - open balance  less (-) units of componente that needed for a production)

my problem is to create cumulative for remaine close balance ( for that i need to create rolling open balance like in the example ) - for next row open balance

my data today looks like this : 

my open balance is the same for all  rows

ProductID	Component_Number	Order_Quantity	Open Balance	Quant For Manufacturing	Units use	CloseBalance	MonthDate	Inbound
A	1	20	150	2	40	110	6	0
A	1	10	150	1	10	140	6	0
A	1	17	100	1	17	83	7	0
B	1	1	100	4	4	96	7	0
B	2	7	70	4	28	42	6	0
B	2	1	70	2	2	68	6	0
A	2	4	40	2	8	32	7	0
B	2	10	40	1	10	30	7	0
B	3	10	700	1	10	690	6	0
B	3	17	700	4	68	632	6	0
A	3	4	758	5	20	738	7	0
B	3	1	738	2	2	736	8	0
B	3	1	738	2	2	736	8	0

And i want to get : 

my open balance is the last close balance for the same component group : 

ProductID	Component_Number	Order_Quantity	Open Balance	Quant For Manufacturing	Units use	CloseBalance	MonthDate	Inbound
A	1	20	150	2	40	110	6	0
A	1	10	110	1	10	100	6	0
A	1	17	100	1	17	83	7	0
B	1	1	83	4	4	79	7	0
B	2	7	70	4	28	42	6	0
B	2	1	42	2	2	40	6	0
A	2	4	40	2	8	32	7	0
B	2	10	32	1	10	22	7	0
B	3	10	700	1	10	690	6	0
B	3	17	690	4	68	622	6	0
A	3	4	622	5	20	602	7	0
B	3	1	602	2	2	600	8	0
B	3	1	60	2	2	58	8	0

* Data : all data store in the same table,

the table containe also orderdate and etc

the inventory is manage monthly level

Inbound - is new inventory that will come in, for the simplicity is 0 now 

my main goal - is to recognize/locate the componente that are out of stock or will be in the future

(will appear  negative in the open balance for future production)

 will appreciate any help

or even a diffrent solution if you have in mind

https://drive.google.com/drive/folders/1ZudPINXPhofTH3P0L1ftm9CKvPRLveLm?usp=sharing

[v-henryk-mstf]

Hi @BlueM ,

Whether the advice given by @amitchandak  has solved your confusion, if the problem has been solved you can mark the reply for the standard answer to help the other members find it more quickly. If not, please point it out.

Looking forward to your feedback.

Best Regards,
Henry

[amitchandak · Accepted solution]

@BlueM , These things usually need to build using initial balance

example of inventory code

Inventory / OnHand
CALCULATE(firstnonblankvalue('Date'[Month]),sum(Table[Intial Inventory]),all('Date')) + CALCULATE(SUM(Table[Ordered]),filter(date,date[date] <=maxx(date,date[date]))) - CALCULATE(SUM(Table[Sold]),filter(date,date[date] <=maxx(date,date[date])))