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

tamerj1's avatar
tamerj1
Community Champion
4 years ago

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:

  • tamerj1's avatar
    tamerj1
    Community Champion
    4 years ago

    Most welcome. Please let me know if you need the Pbi file.

    • anwargabr's avatar
      anwargabr
      Helper I
      4 years ago

      Thanks Dear, I have followed your explanition and creat the Pbi, now if I want to change the interst DAX to reflect the below formula 
      [@Amount]*[@Rate]/360*[@Days]
      Is it applicable as a new column  or new measure?
      also haow can we filter the result by laon number?
      , the result should be exactly same below

       

       




      • tamerj1's avatar
        tamerj1
        Community Champion
        4 years ago

        Good Moring anwargabr 
        Check the file here https://www.dropbox.com/t/poTbyLxMZFOPDbpS
        Not sure if I understand your equation or how you calculated the monthly interest in this table. However, if you need to slice by Loan number then you need to create a measure as calculated columns are no evaluated in the filter context rather in the row context of the iterated table.

        To filter the matrix by loan number you can either use the slicer or just click on the chart column. Also you can slice the chart by month by clicking on the month name in the matrix. Power Bi provides interactive visuals that can filter each other.

        If you have any question regarding the four measures and the two calculated columns in the file, please feel free to let me know. If you find my input hepful and fulfills your requirements, please kudo and mak as Accepted Solution. 
        Thanks and have agreat day

  • anwargabr's avatar
    anwargabr
    Helper I
    4 years ago

    tamerj1 Thanks and much apperciate dear Tamer, I will try it and let you know.