cancel
Showing results for
Did you mean:

Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.

Frequent Visitor

## Calculating column/measure that is dependent on itself

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
2 REPLIES 2
Super User

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?

Regards,
Ashish Mathur
http://www.ashishmathur.com
Frequent Visitor

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.

Announcements

#### Fabric certifications survey

Certification feedback opportunity for the community.

#### Power BI Monthly Update - April 2024

Check out the April 2024 Power BI update to learn about new features.

#### Fabric Community Update - April 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors
Top Kudoed Authors