March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Early bird discount ends December 31.
Register NowBe one of the first to start using Fabric Databases. View on-demand sessions with database experts and the Microsoft product team to learn just how easy it is to get started. Watch 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.
March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount!
Your insights matter. That’s why we created a quick survey to learn about your experience finding answers to technical questions.
Arun Ulag shares exciting details about the Microsoft Fabric Conference 2025, which will be held in Las Vegas, NV.
User | Count |
---|---|
133 | |
90 | |
88 | |
64 | |
58 |
User | Count |
---|---|
202 | |
137 | |
106 | |
70 | |
68 |