Circular Reference Error for an Iterative Formula Applied Over Different Rows of the Same Column

Hi OytunKarabulut 

Recursion is not possible in DAX, as neither measures nor calculated columns can refer to themselves (indirectly or directly).

However, for this particular calculation, you can use a cumulative sum to produce the same results.

Alternatively, you may want to consider calculating this in Power Query with List.Generate.

I have edited your file and attached an example of how this can be done in DAX.

Opening Stock =
VAR CurrentRank = Table1[Rank]
VAR CurrentMaterial = Table1[Material]
VAR OpeningStockLookup =
    CALCULATE (
        SUM ( Table2[Opening Stock] ),
        Table2[Material] = CurrentMaterial
    )
VAR CumulativeConsumption =
    CALCULATE (
        SUM ( Table1[Consumption] ),
        ALLEXCEPT (
            Table1,
            Table1[Material]
        ),
        Table1[Rank] < CurrentRank
    )
RETURN
    OpeningStockLookup - CumulativeConsumption

Closing Stock =
Table1[Opening Stock] - Table1[Consumption]

• Opening Stock subtracts cumulative Consumption (excluding current Rank) from the original Opening Stock from Table2.
• Closing Stock subtracts Consumption for current Rank from Opening Stock.

There are some possible variations on the DAX code above, but the logic should be similar regardless.

Regards,

Owen