Circular reference calculation

Amdyn

Generally recursive calculations can be replicated non-recursively using a combination of cumulative measures, as measures and calculated columns can't self-reference.

See sample model here:

https://www.dropbox.com/s/9zqbq4gvhsg9bba/Circular%20reference%20calculation.pbix?dl=0

This is the output:

 To produce this, let's suppose these are your two source tables:

'Initial Stock' table

Data table
 

 Then this series of measures will produce your closing and opening stock by period:

Initial Stock Measure = 
SUM ( 'Initial Stock'[Intial Stock] )
// This measure should be constant over time and represents Stock at time zero

Production Sum = 
SUM ( Data[Production] )

Market Sum = 
SUM ( Data[Market] )

Production Cumulative = 
CALCULATE (
	[Production Sum],
	FILTER (
		ALL ( Data[Time index] ),
		Data[Time index] <= MAX ( Data[Time index] )
	)
)

Market Cumulative = 
CALCULATE (
	[Market Sum],
	FILTER (
		ALL ( Data[Time index] ),
		Data[Time index] <= MAX ( Data[Time index] )
	)
)

Shortfall Cumulative = 
[Market Cumulative] - ( [Initial Stock Measure] + [Production Cumulative] )

Shortfall Cumulative Maximum So Far = 
MAX ( 
	MAXX (
		FILTER (
			ALL ( Data[Time index] ),
			Data[Time index] <= MAX ( Data[Time index] )
		),
		[Shortfall Cumulative]
	),
	0
)

Closing Stock = 
[Initial Stock Measure] + [Production Cumulative] - [Market Cumulative] + [Shortfall Cumulative Maximum So Far]

Opening Stock = 
CALCULATE ( 
	[Closing Stock],
	FILTER ( 
		ALL ( Data[Time index] ),
		Data[Time index] = MIN ( Data[Time index] ) - 1
	)
)

Anyway, this is just an example to illustrate it can be done :)