Forum Discussion

jennriches's avatar
jennriches
Frequent Visitor
3 years ago

Help with circular references in measures

I am having trouble with a circular reference in my measures.

I believe my biggest issue is with a circular reference between the Opening and the Forecast figure.

 

I have included a OneDrive link to an excel sheet, this is what I am trying to achieve in Power Bi.  

 

Link to my excel table 

 

I have also created a pbix file with some dummy data, and the OneDrive link is below:

 

OneDrive file for PBIX dummy data 

 

I have simplified the measures I was trying to use as I think the main issue i have is the circular reference for the Opening measure as this is trying to use the forecast measure, but I can't think of a way around this as the forecast measure is based on the opening measure for the previous month.

 

These are my measures, it is the Opening measure that has the error:

 

Opening = IF( ISBLANK( [Opening Figure]), CALCULATE([Forecast Closing], DATEADD( 'Date'[Date], -1, MONTH)) , [Opening Figure])
Opening Figure = CALCULATE( SUM(Opening[Opening]))
New Business = CALCULATE ( SUM ( 'New Business'[New Business]))
Withdrawals = CALCULATE( [Opening Figure] * Withdrawals[Withdrawals Value]) * -1
Market Movement = CALCULATE( [Opening Figure] * 'Market Movement'[Market Movement Value])
Forecast Closing = [Opening] + [Market Movement] + [Withdrawals] + [New Business]

 

Thank you in advance for any help!

Need Help

2 Replies

  • lbendlin's avatar
    lbendlin
    Icon for Super User rankSuper User
    3 years ago

    That's not your only problem.  Opening Figure also has a circular reference.

     

    Do these have to be measures?  If you calculate these values in Power Query you can avoid the circular references.