Calculating column/measure that is dependent on itself

[juddmk]

I need help calculating starting/ending balances that are dependent on given inputs for the starting year, but use rates applied to previous year balances to form future year ending balances. Pasting in a table from excel, I am given column B's values and all of rows' 2, 3, and 8's values. My goal is to calculate 2023-2027 rows 5-7 and 9.

My PBI dataset has the years unpivoted so that I have one Year column instead of individual rows. I have tried to use a calculated column and measure, but I usually run into a circular reference because I cannot force the column/measure to calculate for each year and then use the previous year's value as a starting point. I would prefer to calculate inside the model using measures/columns, but can do a function in power query if needed.

	A	B	C	D	E	F	G
1	Year	2022	2023	2024	2025	2026	2027
2	Units	2,159	3,991	4,176	3,907	13,655	10,523
3	Reserves	4,014	4,132	18,278	21,963	44,669	53,876
4								Notes for calcs starting in year 2023
5	Asset Beginning Balance	$49,716	$26,860	$106,768	$134,585	$372,393	$384,164	=Previous Year Asset Ending Balance
6	DD&A Rate	$12.39	$6.50	$5.84	$6.13	$8.34	$7.13	=Current Year Asset Beg Balance/Current Year Reserves
7	DD&A Expense	$26,750	$25,944	$24,391	$23,943	$113,841	$75,036	=Current Year Units*Current Year DD&A Rate
8	New Asset Addition	$3,894	$105,852	$52,208	$261,752	$125,611	$183,449	Given
9	Asset Ending Balance	$26,860	$106,768	$134,585	$372,393	$384,164	$492,577	=CurrentYear Asset Beg Balance-Current Year DD&A Expense+Current Year New Asset Addition

[Ashish_Mathur]

Hi,

Share an MS Excel file with formulas so that your logic can be understood.  How did you arrive at 49,716 in cell B5?

[juddmk]

I edited my original post to say that I am given column B's (2022) values, so I know my starting point. I am having difficulties calculating Column C (2023) rows 6,7,9. I included the formulas in the notes column on the far right of the table.