Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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!
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
49 | |
26 | |
21 | |
15 | |
12 |
User | Count |
---|---|
57 | |
49 | |
44 | |
19 | |
18 |