Get Average of two month on same line item when is Budget

Ganza
Ganza
in Modeling

Hi ,

 

I want just calculate the moving average for the last two months, however once use the following formula                          IF ISACTUALVERSION() THEN Revenue actual ELSE movingsum(Average Revenue,-2,0, AVERAGE)

 

Then circular reference error shows up. Could guys give me hand on how can solve these issues?

I am looking forward to your response

Tagged:

Best Answers

  • M.Kierepka
    M.Kierepka
    Answer ✓

    Hi @Ganza,

    You should update your MOVINGSUM part to movingsum(Average Revenue,-2,-1, AVERAGE). Please, notice that the only change is from 0 to -1 in the third argument (so average of current_month-2 and current_month-1) - you can't reference the same month of the same line item in MOVINGSUM formula, as you would need to know the value before you calculate it, causing circular reference.
    Please keep in mind that this will cause further months to be calculated as average of averages, which may be incorrect from business standpoint.

  • Ganza
    Ganza
    Answer ✓

    Hi @M.Kierepka ,

     

    Thanks so much, you are right, I was referencing the current month which is incorrect.

    My bad 

Categories