## Circular Reference with OFFSET function

Highlighted
Certified Master Anaplanner

## Circular Reference with OFFSET function

I'm getting a circular reference while using the OFFSET function, but I don't understand why.

(1) IF offset_value = -12 THEN OFFSET(Gross Revenue, -12, 0) ELSE OFFSET(Gross Revenue, -11, 0)

vs.

(2) OFFSET(Gross Revenue, offset_value, 0)

Dimension is Months and 'offset_value' always equals either -12 or -11 based on a formula. Why does (1) work but (2) is a circular reference?

Sample below:

2 REPLIES 2
Highlighted
Master Anaplanner/Community Boss

## Re: Circular Reference with OFFSET function

You could set the offset value to cause PY Gross to look at itself even though you know it can never be. Theoretically it could at some point and the system wont allow a user to create a circular reference. With a constant in the formula that can never happen.
Hope that helps.
Highlighted
Contributor

## Re: Circular Reference with OFFSET function

Ryan,

Feel free to use a strict lag/lead in order to avoid having to use the if statement as a solution.

Lag Function