Difference between the PRevious month count and Current Month count

[Vinay07]

Hello all,

I am currently calculating on month's difference count in DAX dynamically. Below is an example of a scenario in the table. Please solve.

The previous Month's count and Current month's count are the Results.

[Anonymous]

Thank @lbendlin  for your prompt reply.

Hi @Vinay07 ,

I have created a data example for you to solve the problem. You can follow the steps below:

1.Add an index column.

2.Add new columns.

PREVIOUS MONTH COUNT = 
VAR _INDEX = 'Table'[Index]
VAR _DT =
    CALCULATE (
        MAX ( 'Table'[INT_RT_EFF_DT] ),
        FILTER ( 'Table', 'Table'[Index] = _INDEX - 1 )
    )
VAR _DATEDIFF1 =
    DATEDIFF ( _DT, 'Table'[INT_RT_EFF_DT], DAY )
VAR _CAPTIL =
    CALCULATE (
        MAX ( 'Table'[LAST_INSNT_CAPTIL] ),
        FILTER ( 'Table', 'Table'[Index] = _INDEX - 1 )
    )
VAR _DATEDIFF2 =
    DATEDIFF ( _CAPTIL, 'Table'[INT_RT_EFF_DT], MONTH )
RETURN
    IF ( _INDEX = 2, _DATEDIFF2, IF ( _INDEX = 1, 0, _DATEDIFF1 / 30 ) )

CURRENT MONTH COUNT = 
VAR _INDEX = 'Table'[Index]
VAR _DT =
    CALCULATE (
        MAX ( 'Table'[INT_RT_EFF_DT] ),
        FILTER ( 'Table', 'Table'[Index] = _INDEX + 1 )
    )
VAR _DATEDIFF1 =
    DATEDIFF ( 'Table'[INT_RT_EFF_DT], _DT, DAY )
VAR _DATEDIFF2 =
    DATEDIFF ( 'Table'[INT_RT_EFF_DT], 'Table'[LAST_UPDATED], MONTH )
RETURN
    IF ( _INDEX = 2, _DATEDIFF1 / 30, IF ( _INDEX = 1, 0, _DATEDIFF2 ) )

Final output:

How to Get Your Question Answered Quickly - Microsoft Fabric Community

If it does not help, please provide more details with your desired out put and pbix file without privacy information.

Best Regards,

Ada Wang

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

[Vinay07]

Hi, @Anonymous @lbendlin Thank you so much for your solution, below are more information about DAX calculation Please help. Thanks in advance!

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

Sample Data looks like the below table: I'm unable to create a table in the text getting an invalid 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 calculated dynamically on only Dates LAST_INST_CAPTIL, INT_RT_EFF_DT, and INT_RT_EFF_DT. Interest rates and Policy Id's 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: Screenshot

[lbendlin]

Please provide sample data that covers your issue or question completely, in a usable format (not as a screenshot).

Do not include sensitive information or anything not related to the issue or question.

If you are unsure how to upload data please refer to https://community.fabric.microsoft.com/t5/Community-Blog/How-to-provide-sample-data-in-the-Power-BI-...

Please show the expected outcome based on the sample data you provided.

Want faster answers? https://community.fabric.microsoft.com/t5/Desktop/How-to-Get-Your-Question-Answered-Quickly/m-p/1447...