Interest Calculation

Hi anwargabr ,
Actually I changed my mind. It seems "at least to me" that it is easier to unpivot the data using dax. I explained earlier how to create a simple date table in DAX. Now all you need to do is to create a new table by CROSSJOIN both Date and Loans tables to generate all combinations then filter down only to the relivant ones as per below code.

FullData = 
VAR FullDateData =
    CROSSJOIN (
        'Date',
        Loans
    )
VAR ExistingDateData =
    FILTER ( 
        FullDateData,
        [Date] > [From]
            && [Date] <= [to]
    )
RETURN
    ExistingDateData 

This is how the original data looks like 

an this the how the unpivoted data looks like

Next you create the relationship between Date and FullData tables.

Last you need to create your measure

Interest Amount = 
SUMX ( 
    FullData,
    DIVIDE ( FullData[Amount] * FullData[Rate], 360, 0)
)

The results 100% matchews the calculations in your excel sheet.

Here is the link to download the Pbi file https://www.dropbox.com/t/axD7brB2Czc8br8V

Hi anwargabr 
Sorry for the very late reply.

Please refer to attached file for two options: 

• Adjusting the calculated table as follows

• Creating a measure directly without creating a calculated table.

Option