How to Calculate Formula based on Previous calculation result

[eddykwan_]

Hi Everyone,

Please help me to give the solution for My expectation.

My Table Calculation :

and this is My logic expression for calculate field COGS, The result for Amount/[GR Qty] is 910,754

My expectation :

I want to calculate field COGS sequentially based on PostingDate.

On the date 25-May-2022, I want to get value with same logic expression but must have value on the previous date (894,432).

Example in the logic based on manual input :

=if(RowNo()=1,(894432),
(((above(if(RowNo()=1,[GR Qty]-SUM(InvoiceQuantity))))*above(TOTAL COGS))+(Amount))
/(above(if(RowNo()=1,[GR Qty]-SUM(InvoiceQuantity)))+[GR Qty]))

Please tell me can I get value 894432 with automatically for calculation on 25 may 2022 ?

Thank you

[deepanshuSh]

I am not sure if I understood correctly, but based on my observation for the calculation you are dependent upon the date, so if you need to be dependent upon the date you can use the max(date) function and put it into a variable and then call that variable in a set analysis within the if condition, thereby getting the required 894432 value based on your latest or required date calculation. If its a monthend date then you can set the value of the variable to the monthend as well and then calculate it. 

Hope it helps!