Compound Interest

Question

Power BI Experts, working on my very first Power BI Report. After modeling the data the output is close to Table on the top. I want to convert that to look like the Table below using DAX (Can't seem to get all month ends, where the data is missing) Any Ideas on Compound Interest will help too in .pbix file. Thanks!

v-juanli-msft · Answer

Hi&nbsp;Anonymous
The table below show some values which i can't find in table above.
it is not clear of the transform rule.
&nbsp;
As a new member, i would suggest you to read this&nbsp;article that will help you get your particular question about Power BI answered quickly and correctly versus seemingly ignored.
&nbsp;
Additionally,Could you select these example data in excel and "copy", then paste it when you reply, so i can directly use your data?
&nbsp;
Best Regards
Maggie
&nbsp;
Community Support Team _ Maggie LiIf this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

anonymous · Answer

v-juanli-msft,&nbsp;&nbsp;Hopefully, this version is more clear.&nbsp;FundNameInvestorContributedAmoutContributedDateInterestRateInterest Type&nbsp;Fund1Investor1$100,000.003/12/20079Compounded-Annually&nbsp;Fund1Investor1$0.0010/1/20079Compounded-Annually&nbsp;Fund1Investor1$100,000.0010/9/20079Compounded-Annually&nbsp;Fund1Investor1$80,000.0012/14/20079Compounded-Annually&nbsp;Fund1Investor1$90,000.003/10/20089Compounded-Annually&nbsp;Fund1Investor1$50,000.005/19/20089Compounded-Annually&nbsp;continued for more dates, funds and Investors…&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;FundNameInvestorAsofdate*Contribution_Till_Date **Days***Cumm_InterestEarned&nbsp;Fund1Investor13/31/2007100,00020&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 493.150.09* 100000 * 20/365&nbsp;&nbsp; i.e&nbsp; (InterestRate* ContributionTillDate* Days/365)Fund1Investor14/30/2007100,00030&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 1,232.88493.15 From Above + 0.09*100000*30/365Fund1Investor15/31/2007100,00031&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 1,997.26&nbsp;Fund1Investor16/30/2007100,00030&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 2,736.99&nbsp;Fund1Investor17/31/2007100,00031&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 3,501.37&nbsp;Fund1Investor18/31/2007100,00031&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 4,265.72&nbsp;Fund1Investor19/30/2007100,00030&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 5,005.48&nbsp;Fund1Investor110/31/2007200,00031&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 6,336.99IR on first 100k for 31 days = 764.38 + IR on next 100k for 23 days = 567.12&nbsp;Fund1Investor111/30/2007200,00030&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 7,816.44&nbsp;Fund1Investor112/30/2007280,00031&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 9,700.27&nbsp;Fund1Investor11/31/2008280,00031&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 11,834.70&nbsp;Fund1Investor12/28/2008280,00029&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 13,831.42&nbsp;Fund1Investor13/31/2008370,00020&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 16,452.73Imp--&gt;Interest will be compounded for first 100k Investment from 3/12/2008&nbsp;Fund1&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;continued for more dates, funds and Investors…&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;*&nbsp; For Every Month End after first contribution&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;** Cummulative Sum&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;*** DateDiff betw ContributedDate &amp; AsofDate (Interest Calculated using number of days)&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;

anonymous · Answer

v-juanli-msft&nbsp;&amp; other&nbsp;Experts -&nbsp;&nbsp;Can someone help?&nbsp;&nbsp;

tommartens · Answer

Hey,
&nbsp;
not sure but maybe you can adapt this approach
https://www.minceddata.info/2018/02/21/using-table-iterators-to-calculate-a-future-value/
to your needs. I created this post some time ago ;-)&nbsp;
&nbsp;
As the this kind of calculation is on the advanced side of DAX, please create an Excel sheet with sample data, that represents your data model, and also contains the expected result. Upload the file to onedrive or dropbox and share the link.
&nbsp;
Regards,
Tom

Forum Discussion

Compound Interest

4 Replies

Related content

Compounding Interest Table

calculate compound interest

table with compound interest in dax

Complex Compounding Interest Rates

Interesting effects with calendar-based time intelligence

FundName	Investor	ContributedAmout	ContributedDate	InterestRate	Interest Type
Fund1	Investor1	$100,000.00	3/12/2007	9	Compounded-Annually
Fund1	Investor1	$0.00	10/1/2007	9	Compounded-Annually
Fund1	Investor1	$100,000.00	10/9/2007	9	Compounded-Annually
Fund1	Investor1	$80,000.00	12/14/2007	9	Compounded-Annually
Fund1	Investor1	$90,000.00	3/10/2008	9	Compounded-Annually
Fund1	Investor1	$50,000.00	5/19/2008	9	Compounded-Annually
continued for more dates, funds and Investors…


FundName	Investor	Asofdate*	Contribution_Till_Date **	Days***	Cumm_InterestEarned
Fund1	Investor1	3/31/2007	100,000	20	493.15	0.09* 100000 * 20/365 i.e (InterestRate* ContributionTillDate* Days/365)
Fund1	Investor1	4/30/2007	100,000	30	1,232.88	493.15 From Above + 0.0910000030/365
Fund1	Investor1	5/31/2007	100,000	31	1,997.26
Fund1	Investor1	6/30/2007	100,000	30	2,736.99
Fund1	Investor1	7/31/2007	100,000	31	3,501.37
Fund1	Investor1	8/31/2007	100,000	31	4,265.72
Fund1	Investor1	9/30/2007	100,000	30	5,005.48
Fund1	Investor1	10/31/2007	200,000	31	6,336.99	IR on first 100k for 31 days = 764.38 + IR on next 100k for 23 days = 567.12
Fund1	Investor1	11/30/2007	200,000	30	7,816.44
Fund1	Investor1	12/30/2007	280,000	31	9,700.27
Fund1	Investor1	1/31/2008	280,000	31	11,834.70
Fund1	Investor1	2/28/2008	280,000	29	13,831.42
Fund1	Investor1	3/31/2008	370,000	20	16,452.73	Imp-->Interest will be compounded for first 100k Investment from 3/12/2008
Fund1
continued for more dates, funds and Investors…

* For Every Month End after first contribution
** Cummulative Sum
*** DateDiff betw ContributedDate & AsofDate (Interest Calculated using number of days)