cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Amdyn Frequent Visitor
Frequent Visitor

Circular reference calculation

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

Column A: Initial stock
Column B: Production
Column C: Market
Column D: 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 ?
1 ACCEPTED SOLUTION

Accepted Solutions
Super User
Super User

Re: 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:Capture3.PNG

 

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

 

'Initial Stock' table

Capture1.PNG

Data table
 Capture2.PNG



 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 Smiley Happy



Did I answer your question? Mark my post as a solution!

Proud to be a Datanaut!




4 REPLIES 4
Highlighted
Super User
Super User

Re: Circular reference calculation

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. 

 

 



* Matt is a Microsoft MVP (Power BI) and author of the Power BI Book Supercharge Power BI.
Super User
Super User

Re: 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:Capture3.PNG

 

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

 

'Initial Stock' table

Capture1.PNG

Data table
 Capture2.PNG



 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 Smiley Happy



Did I answer your question? Mark my post as a solution!

Proud to be a Datanaut!




Amdyn Frequent Visitor
Frequent Visitor

Re: Circular reference calculation

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

Re: Circular reference calculation

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

Much appreciated!