Calculation on opening Balance

ShivankurSharma
Certified Master Anaplanner

Calculation on opening Balance

Hi Anaplanner's,

 

Need a help on a calculation:

 

Have a module where Time is not a dimension, we have system created list to be used as year, and there is a line item where we need to get the ending balance as the opening balance in another line, which formula can help us attain that?

 

In the below screenshot, we need to get the numbers of last line as the opening of next year.

ShivankurSharma_1-1613460933155.png

 

Thank you !!

 

 

4 REPLIES 4
Ingilavicus
Certified Master Anaplanner

Re: Calculation on opening Balance

Hi @ShivankurSharma ,

 

You can simply use a lookup. Best if your dummy year list has consecutive numbers for codes.  

Ingilavicus_0-1613470897927.png

Ingilavicus_1-1613470906581.png

 

Andris

 

MarkWarren
Expert

Re: Calculation on opening Balance

To add to the above solution; for a performance benefit and reusability, I'd suggest a mapping module for the previous years, do the lookup on this line item.
msedge_2021-02-16_11-35-15.png

ShivankurSharma
Certified Master Anaplanner

Re: Calculation on opening Balance

Have tried that even, but I have the circular reference error, if I use it by Lookup.

Misbah
Master Anaplanner/Community Boss

Re: Calculation on opening Balance

@ShivankurSharma  

 

As other champs have mentioned this usually is achieved with the help of LOOKUP but if you are running it in a loop you will get circular references. Hope you are not referring the same line item (Line item B) where you are writing the formula.

 

Formula should be like :

Line item B =  Line item A[LOOKUP: SYS Mapping Module. Mapping Line item] - this should give you the previous year's value if the mapping is done accordingly.

 

If all this doesn't work then I would say convert the module into Native Timescale dimensioned module and use Time based Functions & then reconvert the module back into Custom Time dimensioned module - Not an ideal solution but indeed the last resort for you,

 

Hope that helps

Misbah