Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Amdyn
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
OwenAuger
Super User
Super User

@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!
Blog
Twitter
LinkedIn

View solution in original post

6 REPLIES 6
OwenAuger
Super User
Super User

@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!
Blog
Twitter
LinkedIn

Awesome approach !!

Anonymous
Not applicable

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

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

Much appreciated!

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 an 8 times Microsoft MVP (Power BI) and author of the Power BI Book Supercharge Power BI.

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

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.