Power BI is turning 10, and we’re marking the occasion with a special community challenge. Use your creativity to tell a story, uncover trends, or highlight something unexpected.
Get startedJoin us for an expert-led overview of the tools and concepts you'll need to become a Certified Power BI Data Analyst and pass exam PL-300. Register now.
I am trying to figure out how to build these measures while avoiding a circular dependency. I cannot use a calculated column due to the nature of the report. The only table that exists in the dataset is a one column table for Age that has ages 10-100 listed.
This is what I am going for
Currently I have a Current Balance field that is being calculated as growing 8% every year and a Draw Down amount that is just Current Balance * .04
How can I get the Current Balance measure to subtract the Draw Down Amount and then recalculate the 8% increase based on that new balance?
The formula that I have for Current Balance is this (thanks to SQLBI)
VAR SelectedYear =
SELECTEDVALUE (
'Age'[Age], -- Find the current year in the report
MAX ( 'Age'[Age] ) -- default to the last year available
)
VAR PreviousYears = -- PreviousYears contains the
FILTER ( -- years BEFORE the current one
ALL ( 'Age'[Age] ),
'Age'[Age] <= SelectedYear
)
VAR PreviousInvestments = -- PreviousInvestments contains
ADDCOLUMNS ( -- the amount of all the investments
PreviousYears, -- made in previous years
"@InvestedAmt", CALCULATE (
--SUM ( Investments[Amount] )
[Annual Contribution]
)
)
VAR Result = -- For each previous investment
SUMX ( -- calculate the compound interest
PreviousInvestments, -- over the years and sum the results
VAR InvestmentYear = 'Age'[Age]
VAR InvestmentAmt = [@InvestedAmt]
VAR YearsRateActive =
FILTER (
ALL ( 'Age' ),
VAR YearRate = 'Age'[Age]
RETURN
YearRate >= InvestmentYear
&& YearRate <= SelectedYear
)
VAR CompundInterestRateForInvestment =
PRODUCTX (
YearsRateActive,
1 + 'Annual Return'[Annual Return Value]
--Rates[InterestRate]
)
RETURN
InvestmentAmt * CompundInterestRateForInvestment
)
RETURN
Result
Solved! Go to Solution.
Subtracting 4% is the same as multiplying by 0.96 and adding 8% to that amount is the same as multiplying by 1.08.
EndOfYear balance = StartOfYearBalance * 0.96 * 1.08 = StartOfYearBalance * 1.0368
So, your real annual return is 3.68%.
Try updating your measure to take out the 4% in this variable:
VAR CompundInterestRateForInvestment =
PRODUCTX ( YearsRateActive, 1 + 0.96 * [Annual Return Value] )
Subtracting 4% is the same as multiplying by 0.96 and adding 8% to that amount is the same as multiplying by 1.08.
EndOfYear balance = StartOfYearBalance * 0.96 * 1.08 = StartOfYearBalance * 1.0368
So, your real annual return is 3.68%.
Try updating your measure to take out the 4% in this variable:
VAR CompundInterestRateForInvestment =
PRODUCTX ( YearsRateActive, 1 + 0.96 * [Annual Return Value] )
@AlexisOlson While this wasn't the exact solution I needed to implement to get it to work, this helped me think about it in a different way and got my on the right track. Thank you for the help!
This is your chance to engage directly with the engineering team behind Fabric and Power BI. Share your experiences and shape the future.
Check out the June 2025 Power BI update to learn about new features.
User | Count |
---|---|
11 | |
9 | |
8 | |
8 | |
8 |
User | Count |
---|---|
13 | |
12 | |
11 | |
10 | |
9 |