Circular reference calculation

[Amdyn]

I'm trying to put together a calculation as follows ;

Column A: Initial stock
Column B: Production
Column C: Market
Column 😧 Min(A+B,C)
Column E: A+B - D

This all works ok, but the opening stock for each row in Column A needs to be Column E from the previous period.
So Column A (row n) = Column E (row n-1)

Of course when I try to create this I land up with a circular error( E is dependant on A)
This is of course a trivial excel calculation - the question is ... can it be done in powerbi in some way ?

[OwenAuger · Accepted solution]

@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 🙂

[Vinod_P]

Awesome approach !!

[Anonymous]

Great approach.  However, I see that the "back order" are shortshipped, therefore some orders (market) are lost and we have 65 units at the end.  What do we need to change in the formulas to allow stock to be shipped at a later date when available ?  (therefore end with a zero closing stock).  Tks

[Amdyn]

Thank you for putting this together, I'll work through it and understand it before commenting properly.

Much appreciated!

[MattAllington]

You should not write these as columns - use measures instead. Do you have some way to identify the order of the rows?  E.g. A date?  You will need this - power pivot is a database, not a spreadsheet and hence there's is no Implied order of the rows. 

[Amdyn]

Thanks Matt, I thought as much !
And yes the table has a date column. I could also generate an index.