Circular Reference help

neg177

Hi, 

 

The following formula below is giving me a Circular Reference when it should not be. Please advise on how to avoid this error. The LI that this Formula is in is Acutal and Projected 30 + $ Curve. 

Pic

 

Formula

IF 'Actual 30+ Dollars from Tableau' = 0 AND 'Actual 30+ Dollars from Tableau'[LOOKUP: 's54 MonthonBook Props'.'MOB -1M'] = 0 THEN 'Acutal and Projected 30 + $ Curve'[LOOKUP: 's54 MonthonBook Props'.'MOB -1M'] * 'Deliquency Curve Builder - Update Vintages When Necessary' ELSE 0

alexpavel

@neg177  you reference in the line-item 'Acutal and Projected 30 + $ Curve'.. the same line item, using the LOOKUP:  'Acutal and Projected 30 + $ Curve'[LOOKUP: 's54 MonthonBook Props'.'MOB -1M'] 

 

I presume that 'MOB -1M' is a previous month...Instead of LOOKUP you need to use PREVIOUS or OFFSET function... something like: 

 

IF 'Actual 30+ Dollars from Tableau' = 0 AND 'Actual 30+ Dollars from Tableau'[LOOKUP: 's54 MonthonBook Props'.'MOB -1M'] = 0 THEN 

 

PREVIOUS('Acutal and Projected 30 + $ Curve') * 'Deliquency Curve Builder - Update Vintages When Necessary' ELSE 0

 

 

 

 

obriegr

@neg177

There is a circular reference in your Then Statement. You are referencing back to the original line item you are working on (specifically 'Acutal and Projected 30 + $ Curve'[LOOKUP: 's54 MonthonBook Props'.'MOB -1M'] )

I believe you may have intended to use the 'Actual 30+ Dollars from Tableau' line item.

ChrisAHeathcote

@neg177 

The formula is referencing itself;

 

Actual and Projected 30 + $ Curve = IF 'Actual 30+ Dollars from Tableau' = 0 AND 'Actual 30+ Dollars from Tableau'[LOOKUP: 's54 MonthonBook Props'.'MOB -1M'] = 0 THEN 'Acutal and Projected 30 + $ Curve'[LOOKUP: 's54 MonthonBook Props'.'MOB -1M'] * 'Deliquency Curve Builder - Update Vintages When Necessary' ELSE 0

 

Is this correct?

If so, then try and reference the source of Actual and Projected 30 + $ Curve and calculate the correct result in the true outcome. 

Alternatively, create a new line item and calculate it there. 

ChrisAHeathcote

Great spot. If the assumption regarding MOB-1M is correct in that it is designed to LOOKUP the the previous period then PREVIOUS would be the simplest option.
A great feature of PREVIOUS is that it does not generate a circular reference.

neg177

This is great advice thank you, but we are not looking to look up the PREVIOUS time period which the PREVIOUS function will do, we are trying to retrieve the PREVIOUS 'month on book' which is set in our columns as a List Item. 

neg177

Below is a example of the Excel Formula we are trying to replicate.