Avoiding Circular Reference Error

Anything related to PowerPivot and DAX Formuale
MCurySmith
Posts: 1
Joined: Thu Jan 25, 2018 2:58 am

Avoiding Circular Reference Error

Postby MCurySmith » Fri Jan 26, 2018 3:57 am

Hi,

I am in the process of transitioning a forecasting model from standard excel to power pivot. This involves replicating my exponential smoothing formulas into power pivot measures. I would like to use measures as opposed to calculated columns or power query as I will need to utilize the power pivot model to bring together related data to create the base metrics to be forecasted.

The main difficulty is that the formulas need to access earlier values of themselves which results in circular reference errors. In addition to the self-references the smoothing formulas need to reference exponential factors and initialization values which change infrequently.

I've attached a sample workbook to demonstrate what I'm encountering.

I began by creating versions v1 of each of the three forecast measures without the problematic elements. Here is the code for "FTE Forecast Level v1". I then create v2 and attempted to add the missing term. The add measure formula check was OK, but when I selected OK to add the measure I received a circular reference error message and the measure was not added.

=VAR IndexNmbr = MAX(FTE_Actual[Index])
RETURN
if(IndexNmbr=1,
.886208*[FTE Actual] + (1-.886208)*([FTE Initial Level]+[FTE Initial Trend]),
.886208*[FTE Actual] ***** missing term *****
)

**** Missing Term ****
+ (1-.886208)* CALCULATE([FTE Forecast Next Period v2],
FILTER(FTE_Actual,FTE_Actual[Index] = MAX(FTE_Actual[Index])-1))

As you can see the missing term references the FTE Forecast Next Period from the previous table row. But the formula for the FTE Forecast Next Period also references the previous row of FTE Forecast Level v2, hence the circular reference.

This is not an issue in normal excel because they are seen as different cell references. But I'm at a loss as to how to overcome this in power pivot measures.

Any help would be greatly appreciated.

The 'Initial Input' tab contains the data model input, the formula definitions, example of the excel formulas, etc. The 'FTE Forecast' tab is where I was attempting to workout the measure formulas.

Thanks,
Mike
Attachments
Power Pivot Example.xlsx
(132.25 KiB) Downloaded 96 times

Return to “PowerPivot/DAX”

Who is online

Users browsing this forum: Jonny1010101010 and 7 guests

cron