Circular Calculation Error

[Anonymous]

Hi,

I run into circular dependency problem.

Below is an example.

- Need to calculate EOH unit cost which is [(BOH * BOH unitCost) + (Sale * Sale UnitCost)]/EOH

- The EOH unit cost will become BOH UnitCost of next month.  

Because of this I run into circular calculation ... please help

[Anonymous]

Hi @Anonymous ,

Any update on this? Did the above suggestions help with your scenario? if that is the case, you can consider Kudo or Accept the helpful suggestions to help others who faced similar requirements.

If these also don't help, please share more detailed information and description to help us clarify your scenario to test.

How to Get Your Question Answered Quickly 

Regards,

Xiaoxin Sheng

[Anonymous]

HI @Anonymous,

Perhaps you can take a look at following blog about the circular calculation issue and how to avoid it:

Avoiding circular dependency errors in DAX - SQLBI

Regards,

Xiaoxin Sheng

[littlemojopuppy]

@Anonymous I assume the structure of the table is exactly like shown.

Very first thing, you need a date table!  And after you create the date table, you want to mark it as a date table and then create a relationship from that date table to your fact table.  This is important because it will allow you to use Time Intelligence functions.

After that, this becomes fairly easy.  You create a measure for EOH Unit Cost as you've shown.  You'll create a second measure for EOH Prior Month.  Then use the EOH Prior Month measure as the starting point for this month calculation.

[Anonymous]

That what I did. I first create a date table as the BOH and Sale are in different table.

After that I create column in my date table fo BOH and Sale.

The EOH UnitCost is a function of BOH unit cost.

And the BOH unit cost is a function of previous month EOH Unit Cost.

I think this is how I ran into circular error

[littlemojopuppy]

I prefaced my reply with "I assume the structure of the table is exactly like shown."  Doesn't sound like that's the case.

Let's see sample data from the entire data model.

[Anonymous]

you still can use the data I post. But number is yellow highlight is a calculated number.
EOH cost = (BOH * BOH Cost) - (Sale * Sale Cost)
and BOH cost = previous month EOH cost.

[Ashish_Mathur]

Hi,

Is 5 i.e. value in cell D2, an input value?  Share data in a format that can be pasted in an MS Excel file.

[Ashish_Mathur]

Hi,

Is 5 i.e. value in cell D2, an input value?  Share data in a format that can be pasted in an MS Excel file.