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!
