Interest Calculation

anwargabr , refer to my blog and file, where I have done similar stuff 

https://community.powerbi.com/t5/Community-Blog/How-to-divide-distribute-values-between-start-date-or-end-date/ba-p/1503785

Hi anwargabr ,
First you need to create a table that contains all the dates included in order to calculate the interest per each date. Let's call it "PerDate" table. You can use the following simple code:

PerDate = 
    CALENDAR ( 
        Min ( Loans[From] ),
        Max ( Loans[to] )
    ) ​

In this table create a new month calculated cloumn:

Month = FORMAT ( DATE ( 1, MONTH ( PerDate[Date] ), 1 ) , "mmm" )

Then add another calculated column to calculate the average daily interest as follows:

Interest = 
VAR CurrentDate = 'PerDate'[Date]
VAR CrrentLoans =
    FILTER (
        Loans,
        Loans[From] <= CurrentDate &&
            Loans[to] > CurrentDate
    )
VAR Result =
    CALCULATE ( 
        SUMX (
            CrrentLoans,
            DIVIDE ( Loans[Interest], Loans[Days] )
        )
    )
RETURN
Result

Basically, this code filters the loans table to include only the rows that include each date. Then it iterates over the filtered table to calculate the average interst per each day.

Now you can simply create your measure:

Ineterst ($) = SUM ( PerDate[Interest] )

Then create your report agregated by Month:

Hi anwargabr 

Have you solved this question with tamerj1's help? If you have solved the question, you can accept the answer helpful as the solution or share you method and accept it as solution, thanks for your contribution to improve Power BI.

If you need more help, please let me know.

Best Regards,

Community Support Team _Tang

If this post helps, please consider Accept it as the solution to help the other members find it more quickly.

Gents

The above answer of Mr. tamerj1   is great and helped me to satrt the dashboard. Howerver, another duplication issue came, let me explained:

first, I was preparing this sheet on monthly basis and add new column called "Reporting Month", then at end of each month I copy the data and past it for the new month and do the movemnent "add new loans or make settled loans Zero" so i can track and see the monthly movement of the outstanding loans during the year, it's ok to see the outstanding but when it comes to Interest Calculation, there are some loans booked for more than 30 days duplicated in the new DATE column created by CROSSJOIN table

so if loan ref 1 is from 1 Jan to 30 March, if I just use filter of Reporting Month "Jan", the loan will created 90 rows which is right but when I add Feb and March, it creted another 90 and so on.

If I created a unique number for the loan REF, Is there any DAX could be added to the table to remove any duplication in the created DATE column based on the unique REF, so for each loan REF no duplication DATE.

Thanks