Help avoiding a circular dependency in a measure

Anonymous — Tue, 26 Apr 2022 14:29:29 GMT

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

Re: Help avoiding a circular dependency in a measure

AlexisOlson — Tue, 26 Apr 2022 15:38:45 GMT

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] )

Re: Help avoiding a circular dependency in a measure

Anonymous — Mon, 02 May 2022 13:32:32 GMT

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!

topic Re: Help avoiding a circular dependency in a measure in DAX Commands and Tips

Help avoiding a circular dependency in a measure

Re: Help avoiding a circular dependency in a measure

Re: Help avoiding a circular dependency in a measure