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

Thank you anwargabr 
Now it is clear. It can be done with totally with DAX code but this is not the standard method. As long as you are making a transformation in your work you better follow a professional standard in your data. DAX in either power Bi or Power Pivot likes to deal with Columnar  unpivoted data. That means less columns more rows. In your example that means you need to have the inrest rate and the loan ammount for each bank/loan type for each date date. That would generate a table of more than 1000 rows. This is a an extremely small number of rows compared to what DAX can handle (milions of rows at a time). You can use either excel or power pivot to do that. I can help you out on this but it could be a good excersie for you. 
Next you need a proper Date table which I will help you with it. The rest is a peice of cake.
Try to unpivot your data and I will do something from side as well. Have a great day!

    • tamerj1's avatar
      tamerj1
      Community Champion
      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