Calculation on opening Balance

ShivankurSharma
ShivankurSharma
in Modeling

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 !!

 

 

Tagged:

Answers

  • Ingilavicus
    Ingilavicus

    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
    MarkWarren

    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
    ShivankurSharma

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

  • Misbah
    Misbah

    @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

Categories