Imagine the following Power BI data model:
Account List
| ID | Type |
|---|---|
| ABC | Type1 |
| DEF | Type2 |
Balances
| Date | Account ID | Currency | Credit/Debit | Balance |
|---|---|---|---|---|
| 2024-01-02 | ABC | USD | Credit | 100 |
| 2024-01-02 | DEF | EUR | Debit | 200 |
Interest rates
| Month | Account ID | Currency | Amount From | Amount To | Credit rate | Debit rate |
|---|---|---|---|---|---|---|
| 2024-01 | ABC | USD | 0 | 999 | 0.1 | 0.2 |
| 2024-01 | ABC | USD | 1000 | 9999 | 0.3 | 0.4 |
| 2024-01 | DEF | EUR | 0 | 9999 | 0.5 | 0.6 |
There is also a Date table with unique dates and some calculations, notably YYYY-MM for the respective moth.
Account List contains unique values, but the rest of the tables do not.
There’s a 1:1 relationship between Account List and Balances (ID = Account ID), 1:1 between Account List and Interest Rates (ID = Account ID), 1:1 between Dates and Balances (Date = Date), and Many-to-Many between Dates and Rates (Year-Month = Year-Month).
The goal is to calculate using DAX Balance * Interest Rate, where Interest Rate is either Credit Rate or Debit Rate based on what the balance is marked as in the Balances table, and is derived based on Account ID, Month, Currency and Balance >= Amount From && Balance <= Amount To.
Could someone help me, please?