Referencing same column | inventory calculations | closing and opening inventory

[Anonymous]

I have a requirement to create an inventory forecast by month table where:

InventoryClosing = InventoryOpening + Re-supply - Demand

However, when the demand is higher than InventoryOpening + Re-supply, total inventory should become zero and not negative.  current month's InventoryOpening = last month's InventoryClosing  (InventoryOpening at the start of the period is given)

My Data Table:

My Result columns should look like:

Trying to calculate InventoryClosing using InventoryOpening results a circular reference.
Could not use the cumulating demand and re-supply due to negative inventory values.

Any reccomondation/s would be much appreciated.

[Anonymous · Accepted solution]

Hi @Anonymous ,

like this?

If this can meet your needs, try this measure:

closing inventory = var _value= SUMX(FILTER(ALL('Table'),'Table'[Index]<=MAX('Table'[Index])),'Table'[re-supplied]-'Table'[sales demand])+3500
return if(_value<0,0,_value)

and this is my pbix file for you to refer.

Best regards,

Community Support Team Selina zhu

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly