How to pull previous year end balances into a monthly calc

Options
alismith
alismith
in Modeling

Hi

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.

image.png

Tagged:

Answers

  • Misbah
    Misbah
    Options

    @alismith Try using LAG function instead of OFFSET

  • Venki9902
    Venki9902
    Options

    Hi @alismith ,

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

    image.png

    Step2: module2

    image.png


    Formulas:

    image.png


    hope this helps :).

    Regards,
    Venkatesh

  • alismith
    alismith
    Options

    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
    Venki9902
    Options

    Hi @alismith ,

    Glad you found the solution.

    Regards,
    Venkatesh

Categories