Forum Discussion

gra_197's avatar
gra_197
Icon for Helper II rankHelper II
9 months ago
Solved

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, int...
Need Help
  • v-karpurapud's avatar
    v-karpurapud
    8 months ago

    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.
     

     

ribisht17's avatar
ribisht17
Icon for Super User rankSuper User
9 months ago

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 LOOKUPVALUE to 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

Dance-Sing with Data -BI & Analytics

gra_197's avatar
gra_197
Icon for Helper II rankHelper II
9 months ago

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?