Forum Discussion

anwargabr's avatar
anwargabr
Helper I
4 years ago
Solved

Interest Calculation

Dear Experts,   I need your help and ideas, I have the below data and wante to allocate the monthly interst to each mont, Is ther any ideas?   Thanks    Loan Number Amount Rate From to ...
  • tamerj1's avatar
    tamerj1
    4 years ago

    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

  • tamerj1's avatar
    tamerj1
    3 years ago

    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

anwargabr's avatar
anwargabr
Helper I
3 years ago

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

 

tamerj1's avatar
tamerj1
Community Champion
3 years ago

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