Ending Balance Roll Forward to the Current Period

jspascual

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

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

Thank you,

Jonathan

Accepted answers

ChrisAHeathcote

@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

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

All comments

ChrisAHeathcote

@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

@jspascual

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

See if this helps

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.

ChrisAHeathcote

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

jspascual

@ChrisAHeathcote @Misbah thank you!

Quick Links

Can you help?

Unanswered questions

Batch Script
Hello team, I am logging into Anaplan through SSO. I would like to create a batch script to import a file into Anaplan. For the Spoke model, I would like to run an process via a batch script to import data from the Data Hub. Can anyone provide me script for both the condition.
import monthly data with months in columns
is there a way to import data with months in columns (usual excel table view)? I have to transform the data so there are rows to cover each period in the upload data form, but I am wondering if there is a better way to just import the data as is with the months in the columns. please let me know what you think. thank you,
Cloudworks Big Query Integration Fail
Hello, I am receiving an 'Anaplan Upload Failed' Status Description when testing my integration with a Big Query dataset. The integration imports data from BQ to our Anaplan model. No other details given in the error log. I suspect that Cloudworks is not even picking up the file but am not sure what we did wrong on the set…