Turn on suggestions

Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.

Showing results for

- Microsoft Power BI Community
- Forums
- Get Help with Power BI
- Desktop
- Re: Circular reference calculation

Topic Options

- Subscribe to RSS Feed
- Mark Topic as New
- Mark Topic as Read
- Float this Topic for Current User
- Bookmark
- Subscribe
- Printer Friendly Page

Highlighted

Amdyn

New Member

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

07-17-2017
02:09 PM

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 ?

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 ?

Solved! Go to Solution.

1 ACCEPTED SOLUTION

Accepted Solutions

Highlighted

**Owen Auger**

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

Connect on Twitter

Connect on LinkedIn

OwenAuger

Community Champion

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

07-18-2017
03:59 AM

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 🙂

Connect on Twitter

Connect on LinkedIn

5 REPLIES 5

Highlighted
##

MattAllington

MVP

Re: Circular reference calculation

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

07-17-2017
02:59 PM

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.

Highlighted

**Owen Auger**

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

Connect on Twitter

Connect on LinkedIn

OwenAuger

Community Champion

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

07-18-2017
03:59 AM

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 🙂

Connect on Twitter

Connect on LinkedIn

Highlighted
##

Amdyn

New Member

Re: Circular reference calculation

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

07-18-2017
02:11 PM

Thanks Matt, I thought as much !

And yes the table has a date column. I could also generate an index.

And yes the table has a date column. I could also generate an index.

Highlighted
##

Amdyn

New Member

Re: Circular reference calculation

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

07-18-2017
02:14 PM

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

Much appreciated!

Much appreciated!

Highlighted
##

CarlBI

Regular Visitor

Re: Circular reference calculation

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

02-13-2020
03:31 PM

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

Announcements

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

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

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

Top Solution Authors

User | Count |
---|---|

359 | |

129 | |

92 | |

92 | |

91 |

Top Kudoed Authors

User | Count |
---|---|

472 | |

180 | |

177 | |

137 | |

120 |