cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
New Member

Circular reference calculation

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 ?
1 ACCEPTED SOLUTION

Accepted Solutions
Highlighted
Community Champion
Community Champion

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 🙂


Owen Auger

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

Connect on Twitter
Connect on LinkedIn

View solution in original post

5 REPLIES 5
Highlighted

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.
Highlighted
Community Champion
Community Champion

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 🙂


Owen Auger

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

Connect on Twitter
Connect on LinkedIn

View solution in original post

Highlighted
New Member

Re: Circular reference calculation

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

Re: Circular reference calculation

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

Much appreciated!
Highlighted
Regular Visitor

Re: Circular reference calculation

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

Helpful resources

Announcements
August 2020 Community Challenge: Can You Solve These?

August 2020 Community Challenge: Can You Solve These?

We're excited to announce our first cross-community 'Can You Solve These?' challenge!

Community Blog

Community Blog

Visit our Community Blog for articles, guides, and information created by fellow community members.

Upcoming Events

Upcoming Events

Wondering what events you could join or have an event to promote yourself? Check out our Upcoming Events.

Get Ready for Power BI Dev Camp

Get Ready for Power BI Dev Camp

We are thrilled to announce we will begin running a monthly webinar series named Power BI Dev Camp.

Top Solution Authors
Top Kudoed Authors