How to pull previous year end balances into a monthly calc

So I am having a bit of an issue

I need to do a calculation monthly, but I need to refer back to the previous year end balances as my start point for all months.

I tried doing it with offset, why I take a starting balance for that month, and then offset everything back to December to lookup the amount needed in my calc, based on the month number, but I would get circular references in following line items.

Accepted answers

All comments

Misbah

@alismith Try using LAG function instead of OFFSET

Venki9902

Hi @alismith ,

Please find the solution below:
Step1:Create system module.

Step2: module2

Formulas:

hope this helps :).

Regards,
Venkatesh

alismith

I found Lag gave me the same circular reference.

I found a solution by having my module in the month dimension and then a version in the year, and then looking up the year value for each month.

Venki9902

Hi @alismith ,

Glad you found the solution.

Regards,
Venkatesh

Quick Links

Can you help?

Unanswered questions

Polaris workaround for FIRSTNONBLANK
Polaris doesn't support FIRSTNONBLANK as a function parameter. I have a solution workaround for this already but not too happy with it. So wanted to know what solution workaround people have done to get around this limitation
Looking for Anaplan End-Users to take a short survey!
We are looking for Anaplan end-users to provide feedback on their experiences with the Excel add-in. Interested individuals will respond to this 5-minute survey to help us understand personal needs and behavior when using the add-in. The feedback provided by survey takers is essential to the roadmap of Anaplan's products.…
CERTIFICATION BADGES - ANNOUNCEMENT
Anaplan Champions! The Community team just posted this announcement that certification badges may not be showing up on your profile probably until next year. Rest assured, you will get credit once you complete and pass the exams. https://community.anaplan.com/t5/Blog/Badges-Back-Soon/ba-p/123385