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

[amitchandak]

@OytunKarabulut , You need to use the cumulative formula

Inventory / OnHand
[Intial Inventory] + CALCULATE(SUM(Table[Ordered]),filter(date,date[date] <=Min(date[date]))) -
CALCULATE(SUM(Table[Sold]),filter(date,date[date] <=Min(date[date])))



Inventory / OnHand
CALCULATE(firstnonblankvalue('Date'[Month]),sum(Table[Intial Inventory]),all('Date')) +
CALCULATE(SUM(Table[Ordered]),filter(date,date[date] <min,date[date]))) -
CALCULATE(SUM(Table[Sold]),filter(date,date[date] <min,date[date])))

Power BI Inventory On Hand: https://youtu.be/nKbJ9Cpb-Aw

You can consider window function

Power BI Window function Rolling, Cumulative/Running Total, WTD, MTD, QTD, YTD, FYTD: https://youtu.be/nxc_IWl-tTc