cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
ndesousa Regular Visitor
Regular Visitor

circular reference

2017-08-11_16-48-48.jpg

 

Hi All,

 

Hoping someone can assist in overcoming the dreaded circular reference error in DAX measures.

 

I'm re-building an excel file in Power BI. The circular ref excel file is a "very" cut down version of the original file. The columns in blue are data (only in the excel file - I have them as DAX in my Power BI file) and the others are measures.

 

In essence, I want to track what can be delivered based on what has:

 

  1. Actually delivered
  2. The Ability to Deliver and;
  3. Upcoming demand.

With these 3 inputs, I have to calculate:

 

  1. Carryover (Daily): Difference of available demand and ability to deliver on the day
  2. Usable ability to deliver: Min of available demand and ability to deliver
  3. Available demand: Sum of actual demand plus carryover from previous day. This is the same as Demand for the 1st day of the month. The demand for the 1st day of the month includes the carryover from the previous month.
  4. Actual Carryover: Difference of demand and what was actually delivered
  5. Actual Available Demand: Sum of demand and actual carry over demand from the previous day. This is the same as Demand for the 1st day of the month.
  6. Actual usable ability to deliver: Blank for past days in the month. For days today plus 14, the minimum of Ability to Deliver Actual Available Demand OR the Ability to Deliver for any day(s) post today plus 14.

 

I have entered the excel formulae above the header row. Helping overcome circular reference would be a great quick measure to add to the quick measure toolkit available within Power BI.

 

Your help is greatly appreciated.

2 REPLIES 2
Moderator v-caliao-msft
Moderator

Re: circular reference

@ndesousa,

 

In your sample data, are there only 3 columns, AbilityToDeliver, ActualDeliver and Demand?

 

From your requirement, it should be have AbilityToDemand and ActualDemand. Please provide us more detail sample data, so that we can make further analysis.

 

Regards,

Charlie Liao

ndesousa Regular Visitor
Regular Visitor

Re: circular reference

Hi @v-caliao-msft,

 

Thanks for your response. I've updated the file with additional columns. This is the link

 

Demand is Orders (customer orders)

 

The spreadsheet has 2 worksheets. Version 1 is the original and Version 2 contains the additional and updated columns.

 

I've updated the column headers to be more descriptive.

 

Appreciate your assistance.

 

Regards,

Neville

Helpful resources

Announcements
Virtual Launch Event

Microsoft Business Applications October Virtual Launch Event

Join us for an in-depth look at the new innovations across Dynamics 365 and the Microsoft Power Platform.

Power BI Helps Homeless and Trouble Youth

Power BI Helps Homeless and Trouble Youth

We spoke with Power BI Super User, Greg Deckler, about his charity work

MBAS Gallery

Watch Sessions On Demand!

Continue your learning in our online communities.

Top Kudoed Authors
Users Online
Currently online: 343 members 3,508 guests
Please welcome our newest community members: