Starting December 3, join live sessions with database experts and the Microsoft product team to learn just how easy it is to get started
Learn moreGet certified in Microsoft Fabric—for free! For a limited time, get a free DP-600 exam voucher to use by the end of 2024. Register now
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 |
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?
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.
Starting December 3, join live sessions with database experts and the Fabric product team to learn just how easy it is to get started.
March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Early Bird pricing ends December 9th.
User | Count |
---|---|
94 | |
82 | |
82 | |
70 | |
49 |
User | Count |
---|---|
143 | |
123 | |
107 | |
61 | |
55 |