cancel
Showing results for
Did you mean:
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

## 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:

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

Proud to be a Datanaut!

4 REPLIES 4
Highlighted
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

## 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:

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

Proud to be a Datanaut!

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.
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!