How to obatain daily closing stock value (Recursive calculation)

[LaalBaadshah]

Hi, 

I am facing difficulty while performing a recursive calculation to obtain daily closing stock, 

The problem is as follows, I have opening stock balance for day 1, daily sales qty, order qty(EOQ) and Safety stock qty, using which I have to obatin the closing stock qty for each day.

to calculate closing stock I check first 

if opening stock>safety stock then opening stock -daily sales

else  

opening-daily sales+EOQ

endif

form day 2 onwards the opening stock value will be the closing stock calculated for day 1, i.e.  to calculate closing stock of any days other than day 1, previous day's closing stock must be used as opening stock.

See table 1

I have tried using above function but since it works on existing fields only, it did not gave any solution

Day	Opening Stock	Daily Sales	EOQ	Safety Stock	Closing Stock
1	90	10	30	70	80
2	90	10	30	70	70
3	90	10	30	70	100
4	90	10	30	70	90
5	90	10	30	70	80
6	90	10	30	70	70
7	90	10	30	70	100

[sunny_talwar · Accepted solution]

I think, for third row you should have a closing stock of 90 because your starting is 70 and the daily sales is 10... making is 60 and plus 30 = 90

May be try these expressions

Opening Stock
=Alt(Above(If(RangeSum(Column(1), -[Daily Sales]) >= [Safety Stock], RangeSum(Column(1), -[Daily Sales]), RangeSum(Column(1), -[Daily Sales], EOQ))), [Opening Stock])

Closing Stock
=If(RangeSum(Column(1), -[Daily Sales]) >= [Safety Stock], RangeSum(Column(1), -[Daily Sales]), RangeSum(Column(1), -[Daily Sales], EOQ))

[LaalBaadshah]

thank you very much , this worked exactly how I wanted, Also I want to show the average of these closing stock in a KPI would that be possible.