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

[OytunKarabulut]

Hi all,

I'm trying to build an iterative logic with DAX to calculate Opening and Closing Stocks for specific material & rank combinations. How it should go is that;
- first, I vlookup the Opening Stock value for Rank 1 from a different table. 
- then, I subtract the Consumption from the Opening Stock to come up with the Closing Stock. The Consumptions are dependent on the request, as well as the availability of the opening stock.
- lastly, I need to assign the Closing Stock of Rank 1 as the Opening Stock of Rank 2, and then the same calculation of Closing Stock should happen for Rank 2.

- and, Rank 2 Closing Stock should be equal to Rank 3 Opening Stock, and so on and so forth...

I've created a basic example in Excel where I can refer to individual cells. However, I face a circular reference error when I try to build the same with DAX in Power Pivot or Power BI. I know that DAX calculations are done for each column (and not for each individual cell), however, I still wanted to ask the same to find out if there are any workarounds for this.

When I try to build the same in the Data Modal of the Test File below, I face the circular reference error.

You can see this test file below. The DAX formulas are in the Data Model through the Power Pivot tab.

Test File.xlsx

Thank you very much for your support everyone,
Oytun

[OwenAuger]

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

[OytunKarabulut]

Hi @OwenAuger ,

In the first version of the problem I've written, your solution is applicable, however, when I include another formula to the consumption column, then again I'm stuck with the circular reference error. Can you check this again?

Thanks a lot,

Oytun