Ending Balance Roll Forward to the Current Period

jspascual
Contributor

Ending Balance Roll Forward to the Current Period

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

 

2 ACCEPTED SOLUTIONS

Accepted Solutions
ChrisHeathcote
Community Boss

Re: Ending Balance Roll Forward to the Current Period

@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.

 

Chris Heathcote
Bedford Consulting

Gold Partner and Regional Partner of the Year 2021, EMEA

View solution in original post

Misbah
Moderator

Re: Ending Balance Roll Forward to the Current Period

@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.

View solution in original post

4 REPLIES 4
ChrisHeathcote
Community Boss

Re: Ending Balance Roll Forward to the Current Period

@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.

 

Chris Heathcote
Bedford Consulting

Gold Partner and Regional Partner of the Year 2021, EMEA

View solution in original post

jspascual
Contributor

Re: Ending Balance Roll Forward to the Current Period

@ChrisHeathcote @Misbah thank you! 

 

Misbah
Moderator

Re: Ending Balance Roll Forward to the Current Period

@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.

View solution in original post

ChrisHeathcote
Community Boss

Re: Ending Balance Roll Forward to the Current Period

As stated above the best solution is to convert your list into a sequence of line items.
Chris Heathcote
Bedford Consulting

Gold Partner and Regional Partner of the Year 2021, EMEA