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 |
