Need Help in writing DAX functions 2 get Current, Previous term premium of policy in calculated col

[sudheerbolla]

Hi All, 

@amitchandak , @Ashish_Mathur  , @danextian , @Ritaf1983 , @truptis , @lbendlin, 

I am struggling to write a DAX functions to populate current term policy premium and previous term policy premium in new columns namely Current Term Prem and Previous Term Prem respectively, against the Cancelled policy record to calculate the Change in Premium and % change in premium to give an output of count of policies cancelled within different ranges of % change in Premium. Sample data link is given below.

DAX function should check each and every record in the data set and for each record it has to check

Previous Term Prem:

If a policy Event Code = cancel_policy_non_payment or cancel_policy, check for the previous term no (i.e, term no mentioned against that record - 1) and populate the previous term premium in the "Previous Term Prem" column.. if the policy term of the record in 1, (i.e, {term no mentioned against that record - 1" = 0) it will not have the previous term as it is the first term, in this case, the policy premium of the present term shown against the record with Event Code = create_policy to be populated in the  "Previous Term Prem" column or premium shown against the earliest crediting date of that particular policy.

Note: If policy is in 1st term and a customer cancelled the policy in the same term, we will have 2 records for the same policy. 1st record is for the event policy_created with Crediting date showing the date of creation of the policy. 2nd record is for the event cancel_policy or cancel_policy _non_payment with crediting date showing the cancelled policy date.

Current Term Prem:

If a policy Event Code = cancel_policy_non_payment or cancel_policy, check for the present term no (i.e, term no mentioned against that record) in that record and populate the present premium value for that policy number which is present in the record of same policy with same term no with event code "policy_renewed" or the MAX crediting date of the policy number before cancellation record. Populate it in the "Current Term Prem" column".

Can these be calculated by using the EARLIER function or by using the IF statement. 

Sample data is attached in the EXCEL file and the outcome is in Outcome sheet in the same excel file. 

Data Link: https://www.dropbox.com/scl/fi/tu0xru0joww1w4lo2t9fk/TRANSACTION1.xlsx?rlkey=fyzqy5txj2ucfw9lzzd8yyh...

[sudheerbolla]

Thank you so much. I appreciate you.

For policy No TAL982X762 Prev Term Renewed Prem obtained is 726, (the value which corresponds to the policy_renewed event prior to the cancellation. 

But there is an event called update_policy on 21-09-2022, of premium 115.81. This update policy event on 21.09.2022 has fallen between the Policy_renewed event on 11-08-2022 and cancel_policy_non_payment event on 18.04.2023. In such cases is there any way to add the 115.81 value to the policy renewed event value 726 before cancellation such that 841.81 (726+158.11 = 841.81) value gets populated in the Previous Term Renewed Prem instead of 726 as obtained by DAX function in the file. In general, any update in premium before cancellation and after the immediate policy_renewed event before cancellation of policy to be added policy renewed premium.

Further how to modify the DAX functions of Current Term Premium and Prev Term Renewed Prem to get  values for the policies which are not cancelled (excluding the policies cancelled) for the events create_policy, policy_renewed. 
To get the survival count for increase in the premium.

Added last 4 records of a policy which is active. 

https://www.dropbox.com/scl/fi/tu0xru0joww1w4lo2t9fk/TRANSACTION1.xlsx?rlkey=fyzqy5txj2ucfw9lzzd8yyh...

[lbendlin · Accepted solution]

Here's a framework that computes your columns. You can take it from there to compute the buckets.