DAX Calculation

[Stapes777]

Good day

I have the following:
- Table 1: Database with 1000 rows like this below:

Nr	Loan Amount	Annual interest rate	Start date of loan	Maturity date
1	1 000 000	6.5000%	2015/05/08	2018/05/08

<tbody>
</tbody><colgroup><col><col><col><col><col></colgroup>

- Table 2: Date and Period table

I have linked the "Start Date of loan" column with the dates column in the "Date and period" table

I want to insert a DAX calculation for the database line item that could calculate a value over certain months. To explain in detail:
- I insert a pivot table with months from the "Date and Period table" and then a calculated DAX item in the Values field of the pivot table.
- It will show then the interest amount on a monthly basis between the "Start date of loan" and "Maturity date" of loan

Hope this explains what I want to do, help will be much appreciated!!

 

[scottsen]

Can you give us a manual calculation for a few rows to help us better understand what you want?

 

[Stapes777]

Thanks for the reply! A typical calculation would be:
- For the month of May 2015 the calc wil be;
1 000 000 × 6.5% × 24/365 ( 24 days to end of month from start date)
- June 2015 = 1 000 000 × 6.5% × 31/365
- and so on until the maturity date.

So the idea is that if I relate the date/period table with the database table and then select the months as my row filter it will have all the months down in a row with the values of the above calculation next to the corresponding month.

Hope this will help?

 

[scottsen]

I would try something like this

[Days in Month] := COUNTROWS(Calendar, Calendar[Date] >= MIN(Loans[StartDate]))
[Total Interest] := SUMX(Loans, [Days In Month] * Loans[LoanAmount] * Loans[InterestRate])