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)


(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:

Offset - Circular Reference.PNG

Best Answer

  • DavidSmith
    Answer ✓
    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.