Calculate Remaing Value + Stock Coverage

[_Ma_Do_]

Hi Everybody, 

trying to figure sth our but so far no success with all the other calculations. 

Following data is given: 

Country	Product	Date	Inventory	Demand
Germany	1234	01.12.2022	1000	100
Germany	1234	01.01.2023		200
Germany	1234	01.02.2023		50
Germany	1234	01.03.2023		60
Germany	1234	01.04.2023		30
Germany	1234	01.05.2023		40
Germany	1234	01.06.2023		50
Germany	1234	01.07.2023		200
Germany	1234	01.08.2023		200
Germany	1234	01.09.2023		80
Germany	2222	01.12.2022	1000	100
Germany	2222	01.01.2023		200
Germany	2222	01.02.2023		50
Germany	2222	01.03.2023		60
Germany	2222	01.04.2023		30
Germany	2222	01.05.2023		40
Germany	2222	01.06.2023		50
Germany	2222	01.07.2023		0
Germany	2222	01.08.2023		0
Germany	2222	01.09.2023		0
Germany	2222	01.10.2023		0
Germany	2222	01.11.2023		0
Germany	2222	01.12.2023		0
Poland	1234	01.12.2022	1000	100
Poland	1234	01.01.2023		200
Poland	1234	01.02.2023		50
Poland	1234	01.03.2023		60
Poland	1234	01.04.2023		30
Poland	1234	01.05.2023		40
Poland	1234	01.06.2023		50
Poland	1234	01.07.2023		200
Poland	1234	01.08.2023		200
Poland	1234	01.09.2023		80
Poland	2222	01.12.2022	1000	100
Poland	2222	01.01.2023		200
Poland	2222	01.02.2023		50
Poland	2222	01.03.2023		60
Poland	2222	01.04.2023		30
Poland	2222	01.05.2023		40
Poland	2222	01.06.2023		50
Poland	2222	01.07.2023		50
Poland	2222	01.08.2023		100
Poland	2222	01.09.2023		320
Poland	2222	01.10.2023		0
Poland	2222	01.11.2023		0
Poland	2222	01.12.2023		0

And I'd like to create the Remaining stock + cover calculation to have the following result 

Country	Product	Date	Inventory	Demand	Start Inv	Coverage
Germany	1234	01.12.2022	1000	100	1000	1
Germany	1234	01.01.2023		200	900	1
Germany	1234	01.02.2023		50	700	1
Germany	1234	01.03.2023		60	650	1
Germany	1234	01.04.2023		30	590	1
Germany	1234	01.05.2023		40	560	1
Germany	1234	01.06.2023		50	520	1
Germany	1234	01.07.2023		200	470	1
Germany	1234	01.08.2023		200	270	1
Germany	1234	01.09.2023		80	70	0,875

Start Inv  = Inventory when it's the first entry. Otherwise ending inventory from prev month. 

Coverage will be 1 when Demand < Start Inv and when Inv < Demand then the remainer that can be covered.

I'd be happy to see how to create a new column in dax to have this evaluated. 

Thanks !

M

[johnt75 · Accepted solution]

You can create a couple of calculated columns like

Start Inv = 
VAR CurrentDate = 'Table'[Date]
VAR Inventory = CALCULATE( SUM('Table'[Inventory]), ALLEXCEPT('Table', 'Table'[Country], 'Table'[Product]), 'Table'[Date] <= CurrentDate )
VAR Demand = CALCULATE( SUM('Table'[Demand]), ALLEXCEPT('Table', 'Table'[Country], 'Table'[Product]), 'Table'[Date] < CurrentDate )
RETURN Inventory - Demand

Coverage = IF( 'Table'[Demand] < 'Table'[Start Inv], 1, DIVIDE( 'Table'[Start Inv], 'Table'[Demand] ) )

[_Ma_Do_]

Thanks - that was the thing I was looking to do - I'll jump on the train from this one 🙂