Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

The Power BI DataViz World Championships are on! With four chances to enter, you could win a spot in the LIVE Grand Finale in Las Vegas. Show off your skills.

Reply
Anonymous
Not applicable

Help avoiding a circular dependency in a measure

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

 

tmartin1313_0-1650983168952.png

 

 

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

tmartin1313_1-1650983168954.png

 

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

1 ACCEPTED SOLUTION
AlexisOlson
Super User
Super User

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

View solution in original post

2 REPLIES 2
AlexisOlson
Super User
Super User

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
Not applicable

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

Helpful resources

Announcements
Feb2025 Sticker Challenge

Join our Community Sticker Challenge 2025

If you love stickers, then you will definitely want to check out our Community Sticker Challenge!

Jan NL Carousel

Fabric Community Update - January 2025

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

Top Solution Authors