Opening and Closing Inventory Balance - Forecasting

[gkarop]

Hi all,  new to the forum.

Im having trouble dealing with closing balance of inventory when the forward forecast results in negatives.

I have:

-current starting Inventory

-monthly sales forecast

-monthly incoming inventory

Im using the following

InvMovement:=[PURCHASES]-[SALES]

OPENING INV:=calculate([InvMovement],FILTER(ALL('DATE'[DATE]),'DATE'[DATE]< MAX('DATE'[DATE])))+[CURRINV]

ENDING INV:=calculate([InvMovement],FILTER(ALL('DATE'[DATE]),'DATE'[DATE]<= MAX('DATE'[DATE])))+[CURRINV]

the problem is that there cases where my Sales Team is forecasting above the available inventory for a future period.  When we are out of Stock we still want the forecast to remain, even though we cant fill the sale.

The above OPENING INV and ENDING INV calculations work when inventory is always positive, but accumulates the negatives on Out of Stocks and results in incorrect (negative) opening inventory positions.  

Id like the ENDING INV results to be 0 if it calculates as negative and ENDING INV of the following period to also be zero.

any suggestions?

Thanks

[Ashish_Mathur]

Hi,

Share a simple dataset and show the expected result there.

[gkarop]

Thanks For responding.  This is what Im getting:

	Jan	Feb	Mar	Apr	May	Jun	Jul	Aug	Sep	Oct	Nov	Dec
OPENING INV	120	20	120	20	-80	-180	-280	20	-80	-180	-280	-380
FORECAST	100	100	100	100	100	100	100	100	100	100	100	100
PURCHASES	0	200	0	0	0	0	400	0	0	0	0	0
ENDING INV	20	120	20	-80	-180	-280	20	-80	-180	-280	-380	-480
				FUTURE	FUTURE	FUTURE	FUTURE	FUTURE	FUTURE	FUTURE	FUTURE	FUTURE

This is the desired result:

	Jan	Feb	Mar	Apr	May	Jun	Jul	Aug	Sep	Oct	Nov	Dec
OPENING INV	120	20	120	20	0	0	0	300	200	100	0	0
FORECAST	100	100	100	100	100	100	100	100	100	100	100	100
PURCHASES	0	200	0	0	0	0	400	0	0	0	0	0
ENDING INV	20	120	20	0	0	0	300	200	100	0	0	0
				FUTURE	FUTURE	FUTURE	FUTURE	FUTURE	FUTURE	FUTURE	FUTURE	FUTURE

[Ashish_Mathur]

Hi,

Share the link from where i can download your PBI file.

[gkarop]

Hi was unable to send the PBI file but I mocked up a ppvt file for an illustration of what I want.

Link to file

[gkarop]

Hi, unfortunately im unable to share files as per my company's privacy policies.