Add the prvious year end balance to prinicpal balance for interest calculation

[dawnc63]

I'm trying to create a deposits interest calculator, where interest on deposits is calculated daily and compounded annually.  I've got this far, but I can't seem to figure how to take the total at December 31, 201x and add it to the Prior Year Ending Balance without creating a circular reference error. 

My Interest Running Total measure is:

Interest Running Total =

SUMX(

    FILTER(

        ALLSELECTED(Dates),

        Dates[Date] <= MAX (Dates[Date] )

    ),

    [Daily interest]

)

My Prior Year Ending Balance measure is

Prior Year Ending Balance = CALCULATE (

          [Total Amount],

          FILTER (

               ALL ( Dates ),

                    value(Dates[Year]) <= value(MAX ( Dates[Year] )) - 1

       )

    )

And Total Amount measure is:

Total Amount = sum(Deposits[Amount])

 

My Daily Interest measure is:

Daily interest =

   ROUND(

        CALCULATE([Prior Year Ending Balance]+[Total Amount],DATESYTD(Dates[Date] ))

            * [Applicable Daily Interest Rate],

    4)

 

Applicable Daily Interest Rate measure is:

Applicable Daily Interest Rate =

    LOOKUPVALUE(Rates[Daily_Interest%],Rates[Dates],SELECTEDVALUE(Dates[Date]))

Both my Dates Table (set as a date table) and my Rates Table have consecutive unique date columns and are related by the date column.

 

I understand why I can't add the Interest Running Total measure to the Prior Year Balance, because that is clearly circular. I just can't figure out how to set this up differently so that doesn't happen.
I'm very new to DAX and have spent way too much time trying to figure this out.
Really appreciate any help.
Dawn

[v-alq-msft]

Hi, @dawnc63 

If you take the answer of someone, please mark it as the solution to help the other members who have same problems find it more quickly. If not, let me know and I'll try to help you further. Thanks.

Best Regards,

Allan

[dawnc63]

Thanks for the follow up.  I have not resolved this problem. I added a calculated column, but still got the circular reference.  
The whole thing is broken now because I've reworked the data model, and can't even make it work as well as it was before.
I have signed up for 15 more hours of training, and clearly need to go back to the drawing board.  I appreciate this community and don't want to take up other people's time needlessly.  What I'm really looking for is a couple hours of personal tutoring.  Where would I get that?  I can't seem to find any sources on line that are direcly applicable to what I'm trying to do.  
Not giving up, though!
Thanks,

Dawn

[Ashish_Mathur]

Hi,

I am not sure of how much i can help but i would like to try.  Could you share a dataset and show your expected result there.

[dawnc63]

I am not able to share the data set in a public forum.  I'll keep researching and working on it.

[dawnc63]

Well, it seems that Power BI is not able to do something that is so simple in Excel.  I have tried:

• Adding an index column to my table
• Adding a calculated column

I get a circular reference no matter what I do.

I'm sure I've just missed something in the data model design or in the measure formulas, but honestly, I have spent days studying material on this forum, YouTube, and anything else I can find, and am completely at my wit's end.

Here is a link to my pbix file:
https://townofviewroyal-my.sharepoint.com/:u:/g/personal/dchristenson_viewroyal_ca/Eb3AnnAHrRZAg6ffT...

Your mission, should you choose to accept it, is to figure out how to fill in the blanks in this matrix:

 

• "Balance" should be the prior year's ending balance.
• Interest is calculated as 2% X prior year's ending balance.
• Total, of course, is the sum of 
  
  1. Balance (= prior year's ending balance)
  2. Capital projects
  3. Contributions
  4. Interest

I'm going to have to go back to my good old Excel if I can't resolve this shortly, as I've spent way too much time on it. 
Thanks.

Dawn

[v-alq-msft]

Hi, @dawnc63 

For your error message above, there is a circular reference in your formula. You may break the circle by changing the function, see the similar cases:

https://community.powerbi.com/t5/Desktop/Circular-reference-error/td-p/155321 ,

https://community.powerbi.com/t5/Desktop/Circular-reference-calculation/td-p/213415 .

If I misunderstand the your thoughts, please show us the sample data and  expected output. I am glad to solve the problem for you.

Best Regards,

Allan

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[dawnc63]

Thank you for your help.  I've looked at the linked solutions and I'm thinking that if I can add a calculated column that states the year-end accumulated balance that may solve the problem.  I'll work on it and report back!