Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Get certified in Microsoft Fabric—for free! For a limited time, the Microsoft Fabric Community team will be offering free DP-600 exam vouchers. Prepare now

Reply
anwargabr
Helper I
Helper I

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 NumberAmountRateFromtoDaysInterest
Loan 1 $       5,000.002.00%15-Jan15-Feb        31.00 $       8.61
Loan 1 $       5,000.002.25%15-Feb15-Mar        28.00 $       8.75
Loan 1 $       5,000.002.50%15-Mar15-Apr        31.00 $    10.76
Loan 1 $       5,000.002.50%15-Apr2-May        17.00 $       5.90
Loan 5 $     10,000.002.60%15-Jan15-May     120.00 $    86.67
       $  120.69
MonthInterest     
Jan?     
Feb?     
March?     
April?     
May?     
  $           120.69     
2 ACCEPTED SOLUTIONS

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 
1.png
an this the how the unpivoted data looks like
2.png
Next you create the relationship between Date and FullData tables.
Untitle.png
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

View solution in original post

Hi @anwargabr 
Sorry for the very late reply.

Please refer to attached file for two options: 

  • Adjusting the calculated table as follows

1.png2.png

  • Creating a measure directly without creating a calculated table.

3.png

Option

View solution in original post

14 REPLIES 14
anwargabr
Helper I
Helper I

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

 

Hi @anwargabr 
Sorry for the very late reply.

Please refer to attached file for two options: 

  • Adjusting the calculated table as follows

1.png2.png

  • Creating a measure directly without creating a calculated table.

3.png

Option

v-xiaotang
Community Support
Community Support

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.

tamerj1
Super User
Super User

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:
Report.png

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

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

 

anwargabr_0-1644329421313.png

 




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

Dear @tamerj1 

Thanks for my late reply due to the reason I've explained to you in the private message.


I've attached both excel and Pbi files here
 https://www.dropbox.com/s/hcqqplt4b19sn3a/Test%20Ditribute%20days.rar?dl=0

 

the interest fromua and the manual calcaultion for days are all in the excel sheet.

 

Thanks and Best Regards.

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!

Thanks @tamerj1 

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 
1.png
an this the how the unpivoted data looks like
2.png
Next you create the relationship between Date and FullData tables.
Untitle.png
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 Thanks and much apperciate dear Tamer, I will try it and let you know.

amitchandak
Super User
Super User

@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-o...

 

 

Join us as experts from around the world come together to shape the future of data and AI!
At the Microsoft Analytics Community Conference, global leaders and influential voices are stepping up to share their knowledge and help you master the latest in Microsoft Fabric, Copilot, and Purview.
️ November 12th-14th, 2024
 Online Event
Register Here

@amitchandak Thanks a lot my dear, will tyr to understand the file you've shared and apply it on my own file.

 

Helpful resources

Announcements
OCT PBI Update Carousel

Power BI Monthly Update - October 2024

Check out the October 2024 Power BI update to learn about new features.

September Hackathon Carousel

Microsoft Fabric & AI Learning Hackathon

Learn from experts, get hands-on experience, and win awesome prizes.

October NL Carousel

Fabric Community Update - October 2024

Find out what's new and trending in the Fabric Community.