- Forums
- Get Help with Power BI
- Desktop
- Service
- Report Server
- Integrations with Files and Services
- Mobile Apps
- Developer
- DAX Commands and Tips
- Let's Talk Data
- Custom Visuals Development Discussion
- Community Support
- Welcome to the Community
- Community Feedback
- Community Help Blog
- Training and Consulting
- Dashboard in a Day
- EdX Specific Training Discussion Forum

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

Amdyn

Frequent Visitor

- Mark as New
- Bookmark
- Subscribe
- 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 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 ?

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 ?

Solved! Go to Solution.

1 ACCEPTED SOLUTION

Accepted Solutions

OwenAuger

Super User

- Mark as New
- Bookmark
- Subscribe
- 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

Proud to be a Datanaut!

4 REPLIES 4

Highlighted
##

MattAllington

Super User

Re: Circular reference calculation

- Mark as New
- Bookmark
- Subscribe
- 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.

OwenAuger

Super User

- Mark as New
- Bookmark
- Subscribe
- 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

Proud to be a Datanaut!

Amdyn

Frequent Visitor

Re: Circular reference calculation

- Mark as New
- Bookmark
- Subscribe
- 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.

Amdyn

Frequent Visitor

Re: Circular reference calculation

- Mark as New
- Bookmark
- Subscribe
- 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!