Ending Balance Roll Forward to the Current Period

jspascual
jspascual
in Modeling

Hello, 

 

I hope you are all doing well.

 

I need some help on my calculation. 

 

Issue is, I need my ending balance of the prior period to be the beginning balance of the current period. 

The thing is, these movements (beginning +/- movements = ending balance) are all members of the same list. 

It just does not come forward. 

 

The formula goes like this:

 

IF ITEM(PPE Cost Movements) = PPE Cost Movements.Beginning Balance

THEN PREVIOUS(Line Item in Issue[SELECT: PPE Cost Movements.Ending Balance]) ELSE 0

 

jonathanpascual_0-1617804743680.png

It should take the 284M as the beginning for Jan 20 and not the loaded 295M anymore.

 

Thank you,

 

Jonathan

 

Tagged:

Best Answers

  • ChrisAHeathcote
    ChrisAHeathcote
    Answer ✓

    @jspascual 

    Try setting up a staging module where the list is not a dimension.

    Use PREVIOUS(Line Item in Issue[SELECT: PPE Cost Movements.Ending Balance]) to select the ending balance of the previous month.

    Reference this line item in your IF THEN ELSE.

     

  • Misbah
    Misbah
    Answer ✓

    @jspascual 

     

    PREVIOUS function works on TIME only. It will not return the Previous value of any other list item.

     

    See if this helps

    1. Instead of Using Beginning balance, Ending balance and other items as a list, try creating a line item for each list item. Note: You will have to create a LOOKUP module to pull the values into line items

    2. Once you have created line items for each list item write a formula in Beginning Balance line item as

     

    Beginning Balance = If First Period of the Timescale then Source Module.Beginning Balance Else PREVIOUS(Ending Inventory

     

    Let me know if you want to mimic it up in Anaplan.

Answers

Categories