Circular Reference with OFFSET function

[ryan_kohn]

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:

[DavidSmith · Accepted solution]

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.

[greg_kajewski]

Ryan,

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

Lag Function