Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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!
Hey,
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 😉
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.
Regards,
Tom
Hi @Anonymous
The table below show some values which i can't find in table above.
it is not clear of the transform rule.
As a new member, i would suggest you to read this article that will help you get your particular question about Power BI answered quickly and correctly versus seemingly ignored.
Additionally,Could you select these example data in excel and "copy", then paste it when you reply, so i can directly use your data?
Best Regards
Maggie
Community Support Team _ Maggie Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
v-juanli-msft,
Hopefully, this version is more clear.
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.09*100000*30/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) | ||||||
v-juanli-msft & other Experts -
Can someone help?
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
112 | |
100 | |
80 | |
64 | |
57 |
User | Count |
---|---|
145 | |
111 | |
92 | |
84 | |
66 |