Showing results for 
Search instead for 
Did you mean: 
Frequent Visitor

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!

Super User
Super User

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.

Helpful resources

Vote for T-Shirt Design

Power BI T-Shirt Design Challenge 2023

Vote for your favorite t-shirt design now through March 28.

March 2023 Update3

Power BI March 2023 Update

Find out more about the March 2023 update.

March Events 2023A

March 2023 Events

Find out more about the online and in person events happening in March!

Top Solution Authors