Help avoiding a circular dependency in a measure

[Anonymous]

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

[AlexisOlson · Accepted 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] )

[Anonymous]

@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!