circular reference

[Anonymous]

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.

[v-caliao-msft]

@Anonymous,

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

[Anonymous]

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