Forum Discussion
Referencing same column | inventory calculations | closing and opening inventory
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.
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
1 Reply
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