Circular Dependency in Calculating Interest & Avg Daily Balances

[robz]

I’m trying to move a report from excel to PowerBI that accounts for an investment funds’ balance and investors’ balances.

Essentially there is one investment portfolio that holds interest-bearing assets.  There are 7 contributors that can invest/withdraw cash throughout the month from the investment account.  At the end of the month, based on the average daily balance (for the month) of each contributor they receive their share of that interest earned for the month which is added to their balance going forward.  The daily balance = total contributed funds + prior months interest.

The primary data inputs are an “Interest” table, a “Cash Transfers” table, and a Date table.

Interest

• Period Start Date
• Period End Date
• TotalInterest

Cash Transfers

• ContributorID
• Transfer Date
• Amount Deposited/(Withdrawn)
• Notes

I originally generated a table of all contributorID’s and days with CROSSJOIN of  values(Dates[Date])  and values(Cash Transfer[ContributorID]).   Then using the row context and SUMX , I created calculated columns for ‘Running Total Deposited/(Withdrawn)’ from the ‘Cash Transfers’ data for each account and separate column to calculate the porfolio’s total principal balance. Using sumx I found the average monthly balance (pre – interest).  Where I get into trouble is trying to create a measure or calculated column that can calculate the interest.

Because prior month interest is an input into current and future month’s average daily balance (an input to determining the interest), I keep running into circular dependency errors. I haven't been using multiple calculate functions in the table so it's not from that...

At the end of the day I’m trying to calculate:

Monthly Interest by contributorID”

“Ending monthly balance by contributorID”. 

Any ideas on how to get there in DAX or PowerQuery ? 

Interest TableCash Transfer Table

Thanks!

[robz]

I’m trying to move a report from excel to PowerBI that accounts for an investment funds’ balance and investors’ balances.

Essentially there is one investment portfolio that holds interest-bearing assets.  There are 5 contributors that can invest/withdraw cash throughout the month from the investment account.  At the end of the month, based on the average daily balance (for the month) of each contributor they receive their share of that interest earned for the month which is added to their balance going forward. 

The primary data inputs are an “Interest” table, a “Cash Transfers” table, and a Date table.

Interest

• Period Start Date
• Period End Date
• TotalInterest

Cash Transfers

• ContributorID
• Transfer Date
• Amount Deposited/(Withdrawn)
• Notes

I originally generated a table of all contributorID’s and days with CROSSJOIN of  values(Dates[Date])  and values(Cash Transfer[ContributorID]).   Then using the row context and SUMX , I created calculated columns for ‘Running Total Deposited/(Withdrawn)’ from the ‘Cash Transfers’ data for each account and separate column to calculate the porfolio’s total principal balance. 

Using sumx I found the average monthly balance (pre – interest).  Where I get into trouble is trying to create a measure or calculated column that can calculate the interest because prior month interest is an input into current and future month’s average daily balance (an input to determining the interest).  I’ve tried a few different things but I keep getting circular references. 

At the end of the day I’m trying to calculate,

"Monthly Interest by contributorID” and “Monthly Ending balance by contributorID”= cash transfers + interest accrued

Any ideas on how to calculate those in DAX or PowerQuery ?

Thanks!

 Cash TransfersPortfolio Interest Earned

[Anonymous]

HI @robz,

I' d like to suggest you take a look at following blogs which told about circular dependency error and how to avoid this:

Avoiding circular dependency errors in DAX

Understanding Circular Dependencies in Tabular and PowerPivot

If above not help, can you please share a pbix file with some sample data for test?

Regards,

Xiaoxin Sheng