Forum Discussion
Adding an intererst rate
Hi all,
Help needed
I have 2 tables, one with invoices outstanding and the other one has 2 columns month and interst rate.
Whats the best way of showing in a table the invoice number, interest rate applicale based on month and then the owed interest amount (days outstanding x interest rate)
Thanks
Hi gra_197
Sure, these are the measures I included in the report. Please review them and share your feedback.
Interest_Rate % = MAX ( InterestRates[Interest Rate %] )Interest Amount = VAR Rate = [Interest_Rate %] / 100 VAR Amount = MAX ( Invoices[Price] ) VAR Days = MAX ( Invoices[Days Overdue] ) RETURN Amount * Rate * Days / 365
If you have any more questions, please let us know and we’ll be happy to help.Regards,
Microsoft Fabric Community Support Team.
17 Replies
Hi,
Share data in a format that can be pasted in an MS Excel file. In another tab, show the expected result very clearly.
Hello gra_197
This is a pretty common scenario in Power BI where you’ve got invoices with outstanding amounts and a separate table of monthly interest rates. The cleanest way to handle it is to connect both through a proper Date table so each invoice automatically picks up the right month’s rate. Once that relationship is in place, you can use a simple DAX expression like
LOOKUPVALUEto fetch the applicable interest rate for the invoice’s month, and then calculate the owed interest by multiplying the days outstanding, the interest rate, and the invoice amount. Depending on whether your rate is daily or monthly, you might adjust the formula slightly, but the idea is the same: tie the invoice date to the right rate and compute the interest owed. Finally, drop those fields into a Power BI table visual so you can see invoice number, interest rate, days outstanding, and owed interest all in one place.Regards,
Ritesh
Community Champion
Please mark the answer if helpful so that it can help others
I have the date table set up and a relationship created between the two tables however the Interest rates are still not populating.
In order to pull in interest rates,Ive set up the table like so
01/10/2025. 2%
01/11/2025. 3%
01/12/2025. 4%
01/01/2026. 5%
01/01/2026. 6%
And I wonder is the reason it's not pulling the data in,because I have the date as the first of the month and if there are no invoices for this date, it won't pull any data?
You cannot do that in a table. Needs a matrix visual, and it will become awkward quickly if you have more than a handful of invoices and more than a dozen months. Instead, consider aggregating your data (for example by ditching the invoice number details)
I have the date table set up and a relationship created between the two tables however the Interest rates are still not populating.
In order to pull in interest rates,Ive set up the table like so
01/10/2025. 2%
01/11/2025. 3%
01/12/2025. 4%
01/01/2026. 5%
01/01/2026. 6%
And I wonder is the reason it's not pulling the data in,because I have the date as the first of the month and if there are no invoices for this date, it won't pull any data?
having trouble uploading
link is blocked, asks for access.
I want in a visual to bring forward the debtor, the amount, the number of days overdue, the %interest applied and the total value of the interest
Please provide sample data that covers your issue or question completely, in a usable format (not as a screenshot).
Do not include sensitive information. Do not include anything that is unrelated to the issue or question.
Please show the expected outcome based on the sample data you provided.
Need help uploading data? https://community.fabric.microsoft.com/t5/Community-Blog/How-to-provide-sample-data-in-the-Power-BI-Forum/ba-p/963216
Want faster answers? https://community.fabric.microsoft.com/t5/Desktop/How-to-Get-Your-Question-Answered-Quickly/m-p/1447523
- Make sure the data type of 'RatesTable'[Month] and Invoices[Date] is Date (not Date/Time or Text).
- The values in 'RatesTable'[Month] should exactly match the first day of each month (e.g., 2025-01-01, 2025-02-01, etc.).
Adjust the names of tables and fields in the next measure as needed.
Interest Rate = LOOKUPVALUE( 'RatesTable'[Interest Rate], // the column you want to return 'RatesTable'[Month], // search column in rates table STARTOFMONTH( MIN(Invoices[Date] )), // Convert date to 1stDayOfMonth 0 // alternate result (date not available in RatesTable) )i hope this helps. if so kudos are welcome 🤓
Hi gra_197
Thank you for contacting the Microsoft Fabric community forum.
The issue was related to the data model rather than the visual itself. I have implemented a DAX-based solution using sample data to demonstrate the correct approach, ensuring the interest rate is applied at invoice level based on the invoice month. I’ve attached the PBIX file along with a snapshot of the final output for reference. Please take a moment to review them and let me know your observations .
If you have more questions, please let us know and we’ll be glad to assist.
Best regards,
Microsoft Fabric Community Support Team.
v-karpurapud I have been unable to access the pbix on my phone. Are you ok to share the measure please?
Hi gra_197
Sure, these are the measures I included in the report. Please review them and share your feedback.
Interest_Rate % = MAX ( InterestRates[Interest Rate %] )Interest Amount = VAR Rate = [Interest_Rate %] / 100 VAR Amount = MAX ( Invoices[Price] ) VAR Days = MAX ( Invoices[Days Overdue] ) RETURN Amount * Rate * Days / 365
If you have any more questions, please let us know and we’ll be happy to help.Regards,
Microsoft Fabric Community Support Team.
Hi gra_197
I wanted to check if you’ve had a chance to review the information provided. If you have any further questions, please let us know. Has your issue been resolved? If not, please share more details so we can assist you further.
Thank You.Hi gra_197
We have not received a response from you regarding the query and were following up to check if you had the opportunity to review the information provided. Please feel free to contact us if you have any further questions.
Thank You.
