DAX Calculatation on new proportional interest rate change from Old to New interest rate change date

[Vinay07]

Hi,

Here are more details about the New proportional interest rate change calculation that needs to be done. Please help

Sample Data looks like the below table: I'm unable to create a table here getting an HTML error below is the screenshot for reference.

In the above table last two records are new interest rate changes on the sample data and calculations need to be worked. Please find below a calculation example.

1st Record : INT_RT_ID : TDSRVB
Interest Rate : 0.5 %
0.05÷100=0.0005
0.0005 × 6 ÷ 12 = 0.00025 --> LAST_INST_CAPTIL (2023/7) - LAST_UPDATED (2024/1) = 6 Months
SURVIVAL : [SURV_DEFR_INT_AMT (10000) + [SURV_DEFR_DIV_AMT (100)]
Settlement interest: (10000+100)x 0.00025 = 2 (rounded to the nearest yen)

------------------------------------------------

2nd Record : INT_RT_ID : MLJDIV
Interest Rate : 0.25 %
0.25÷100=0.0025
0.0025 × 6 ÷ 12 = 0.00125 --> LAST_INST_CAPTIL (2023/7) - LAST_UPDATED (2024/1) = 6 Months
SURVIVAL : [SURV_DEFR_INT_AMT (10000) + [SURV_DEFR_DIV_AMT (100)]
Settlement interest: (10000+100)x 0.00125 = 12 (rounded to the nearest yen)

The above calculations are old interest rates on TDSRVB & MLJDIV. Below are the proportional interest rate changes on DHYSRV.

Here Calculation needs to be calculate dynamically on only Dates LAST_INST_CAPTIL, INT_RT_EFF_DT, and INT_RT_EFF_DT. Interest rates and Policy IDs may be different not fixed.

LAST_INST_CAPTIL : 2023/7

1st Interest changed from 0.10 to 0.20

INT_RT_EFF_DT : 2023/10

2nd Interest changed from 0.20 to 0.25

INT_RT_EFF_DT : 2023/11

LAST_UPDATED : 2024/1

1st interest : 0.0010X3 ÷12= 0.0003(round off to four decimal place) --> LAST_INST_CAPTIL (2023/7) - INT_RT_EFF_DT (2023/10) 3

2nd interest : 0.0020X1 ÷12= 0.0002(round off to four decimal place) --> INT_RT_EFF_DT (2023/10) - INT_RT_EFF_DT (2023/11) = 1

3rd interest : 0.0025X2 ÷12=0.0004(round off to four decimal place) --> --> INT_RT_EFF_DT (2023/11) - LAST_UPDATED (2024/1) = 2

SURVIVAL : [SURV_DEFR_INT_AMT (10000) + [SURV_DEFR_DIV_AMT (100)] --> next to the POlicy_ID

Settlement interest: (10000+100)×0.0003+(10000+100)×0.0002+(10000+100)×0.0004 =3.03+2.02+4.04=9.09 (Round off) =9

FINAL OUTPUT:

[Anonymous]

Hi @lbendlin ,Thanks for the quick reply, I'll add to it.

Hi @Vinay07 ,

The Table data is shown below:

Please follow these steps:
1. Creating Indexed Columns with Power Query

2.Use the following DAX expression to create a column

Column = IF('Table'[INT_RT_ID] = "DHYSRV",
SWITCH(TRUE(),
'Table'[Index] = 3,'Table'[LAST_INST_CAPTIL],
'Table'[Index] = 4,'Table'[INT_RT_EFF_DT],
'Table'[Index] = 5,'Table'[INT_RT_EFF_DT]),
SWITCH(TRUE(),
'Table'[INT_RT_ID] = "TDSRVB",'Table'[LAST_INST_CAPTIL],
'Table'[INT_RT_ID] = "MLJDIV",'Table'[LAST_INST_CAPTIL]))

3.Use the following DAX expression to create a column

Column 2 = IF('Table'[INT_RT_ID] = "DHYSRV",
SWITCH(TRUE(),
'Table'[Index] = 3,SUMX(FILTER('Table','Table'[Index] = 4 ),'Table'[INT_RT_EFF_DT]),
'Table'[Index] = 4,SUMX(FILTER('Table','Table'[Index] = 5 ),'Table'[INT_RT_EFF_DT]),
'Table'[Index] = 5,'Table'[LAST_UPDATED]),
SWITCH(TRUE(),
'Table'[INT_RT_ID] = "TDSRVB",'Table'[LAST_UPDATED],
'Table'[INT_RT_ID] = "MLJDIV",'Table'[LAST_UPDATED]))

4.Use the following DAX expression to create a measure named 'Final output'

Final output = 
 VAR _a =  CALCULATE(TRUNC(SUMX(FILTER('Table','Table'[Index] = 3),ROUND(('Table'[INIT_DPOS_INT_PCT] / 100  * DATEDIFF('Table'[Column],'Table'[Column 2],MONTH) / 12),4) * ('Table'[SURV_DEFR_INT_AMT] + 'Table'[SURV_DEFR_DIV_AMT]))),ALL('Table'))
 VAR _b = CALCULATE(TRUNC(SUMX(FILTER('Table','Table'[Index] = 4),ROUND(('Table'[INIT_DPOS_INT_PCT] / 100  * DATEDIFF('Table'[Column],'Table'[Column 2],MONTH) / 12),4) * ('Table'[SURV_DEFR_INT_AMT] + 'Table'[SURV_DEFR_DIV_AMT]))),ALL('Table'))
 VAR _c = CALCULATE(TRUNC(SUMX(FILTER('Table','Table'[Index] = 5),ROUND(('Table'[INIT_DPOS_INT_PCT] / 100  * DATEDIFF('Table'[Column],'Table'[Column 2],MONTH) / 12),4) * ('Table'[SURV_DEFR_INT_AMT] + 'Table'[SURV_DEFR_DIV_AMT]))),ALL('Table'))
 RETURN
IF(SELECTEDVALUE('Table'[INT_RT_ID]) = "MLJDIV" || SELECTEDVALUE('Table'[INT_RT_ID]) = "TDSRVB",
 TRUNC(SELECTEDVALUE('Table'[INIT_DPOS_INT_PCT]) / 100 * DATEDIFF(SELECTEDVALUE('Table'[Column]),SELECTEDVALUE('Table'[Column 2]),MONTH) / 12 * (SELECTEDVALUE('Table'[SURV_DEFR_INT_AMT]) + SELECTEDVALUE('Table'[SURV_DEFR_DIV_AMT]))),
_a+_b+_c)

5.Final output

Best Regards,
Wenbin Zhou
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Vinay07]

Hi @Anonymous, Thank you so much for your solution. 

Today when I'm working on your DAX solution. I have loaded the data into the PBI desktop and got a lot of records from my Database. Please help
Can we calculate in DAX without adding the "INDEX" column from Power Query? Because the number of records is almost more than 1K. Below is the screenshot for your reference. I am unable to upload the .pbix file here. Please help. 

[Anonymous]

Hi @Vinay07 ,

Regarding your question,If you plan to use only the 'LAST_INST_CAPTIL' and 'LAST_UPDATED' columns of the current row to calculate the number of months of variance, you can do so without creating index columns.Since the 'POLCY_ID' value is not unique, we need to create unique index columns to determine which corresponding row data to use for calculating the month difference.If you think there is too much data, you can filter the required data in Power Query and then create the index columns.

Best Regards,
Wenbin Zhou
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[lbendlin]

Please provide a more detailed explanation of what you are aiming to achieve. What have you tried and where are you stuck?

[Vinay07]

1. To aim this report to achieve the latest effective interest change from the previous date to the current date
2. The above calculation formula will be applicable when the interest revision happens during the fiscal year. In this case, proportional interest rate calculation needs to be done. 
3. I have tried different approaches and methods to work on the difference between the two dates to get the month's count. As I requested in the previous post: (Difference between the PRevious month count and Current Month count). 
4. But in this post above FINAL OUTPIT screenshot is the table visual to show the result on "DIFFERENCE MONTHS COUNT", "PROPORTIONAL" and "SETTLEMENT CALCULATION"
5. I have stuck in the "DIFFERENCE MONTHS COUNT" calculations. Please help me.
6. Below is the DAX I have tried in Power BI

      DAX: DATEDIFF('Sample 3'[LAST_INST_CAPTIL],'Sample 3'[INT_RT_EFF_DT],MONTH)