Circular Dependency

[Kaustubh_94]

Hi Guys,
I'm facing a circular depedency error.
Here is the background:
I have to create a calculated column, which has the logic... if the month number is 1 then a result from a column imported from database (this part is working), if month number is NOT 1 then it has to look for the previous value of addition of two current column values.

Now, the addition of two columns --> these two columns are also have to be a calculated column which is referring back to the column.
let's say the logic is in the 6th cell:

Z = If ( month6 <>1, AA5+AC5, K6 )
AA = Z-V
AC = AA-K

As Here in formula Z, if the month value is not 1 then it has to go to the previous row of addtion of two values of a calculated column.

The dax formulas are:

Open Inv BI =

VAR A =    

    IFERROR

    (

        IF

        (

            bottomup_inputdata[month] <> 1 ,

            LOOKUPVALUE(bottomup_inputdata[part -i open inv bi], bottomup_inputdata[Index],bottomup_inputdata[open Inv BI prev index]) ,

            bottomup_inputdata[_bu_year_monthly_open_asset_inventory_units]

        ),

    0

    )

RETURN

CALCULATE(1* A, ALLEXCEPT(bottomup_inputdata,bottomup_inputdata[Key]))

Now in order to look for previous row, I've created an index and prev index, and put that in the lookupvalue function (lookup is working). But when it comes to col AA and AC it referes back to Z.

In the formula above:
AA+AC is 

part -i open inv bi =

CALCULATE

(

    SUMX(

        bottomup_inputdata,

        bottomup_inputdata[Measure Open inv BI Step -1] + bottomup_inputdata[Injection (units) BI]

        ),

    ALLEXCEPT( bottomup_inputdata,bottomup_inputdata[Key],bottomup_inputdata[b_id],bottomup_inputdata[country])

)

Open inv BI Step -1 =

CALCULATE

(

    SUMX

        (

            bottomup_inputdata,

                bottomup_inputdata[open inv bi] - bottomup_inputdata[Losses Non return] - bottomup_inputdata[Losses breakage] + bottomup_inputdata[bu_year_transfers_units]

        ),

 ALLEXCEPT(bottomup_inputdata,bottomup_inputdata[Key],bottomup_inputdata[b_id],bottomup_inputdata[country])

)

In the last formula it's referring back to 1st formula, this is circular error.

pasting image for error.

Solution Tried:

1. Making measure instead of columns: at the end lookupfunction has to be used, and in that col input is mandatory.
   1. Breaking up the formula: done this as well, but as soon as it gets to measure, i have to look for month value in first formula (open Inv BI), col names doesnt come in measure, need to use any function, now in order to find the respective month value, either i use lookup or any other function. (means its creating the same issue here again, lookup the value, again lookup the value and it goes on).
2. Table Relationship: There's no issue in table relationship, as it is being done in the same table, there's no other reference table.
3. Creating table for each col: Not a dynamic solution as this Open Inv BI formula reference is used in more than 15 col, and based on this many col are interconnected. 
4. Using Calculate function:
   1. created with calculated function with all except to primary key.
   2. Also tried creating with calculated function with all except to combination of other col which are geeting used.

@Idrissshatila @Ahmedx @amitchandak @Ritaf1983 @parry2k @Greg_Deckler @Ashish_Mathur @MFelix 
Do give some advice, on how to solve this error

[Idrissshatila]

Hello @Kaustubh_94 ,

try creating it on the power query level, this may work.

[Kaustubh_94]

At power query level, I think we cannot create calculated columns based on another calculated columns. Column can only be created based on columns which are imported from data source.

[Idrissshatila]

yes, exactly so create the first created column in power query.

[Kaustubh_94]

this is the issue here... 1st created column depends upon 2nd created col...and the 2nd created col depend on the 1st... its like a=b+c where b=a+d... where a,b,c,d are col to be created, so how do i do it in power query.

[Idrissshatila]

@Kaustubh_94 ,

this won't work like that, you need to have a place to start with that is available, you can't reference a column that doesn't exists to be the value.

[Kaustubh_94]

Thanks...
Is there a way to create an instance of the col that does not exist... because this formula of inter dependency of col works in MS- Excel, but with a warning. So is there a way to replicate this in power bi.