DAX function to populate present & prev term prem against only the latest record of same event codes

[sudheerbolla]

Hi Everyone,

@amitchandak , @Ashish_Mathur  , @danextian , @Ritaf1983 , @truptis , @lbendlin, @Ritaf1983 ,@VilmarSch , @Kishore_KVN , @Ahmedx 

Please help in writing a DAX function to  populate present & prev term prem for Active policies against only the latest record of event code "policy_renewed" . A policy can have multiple event code of "policy_renewed" for same policy number as the customer renewed the policy for 2nd, 3rd, 4th term.

The following DAX Fuction is returning the values for all the "policy_renewed" event codes in a policy.

Current Term Prem =

if([Event Code] in {"policy_renewed"},

var md = [Transaction Date] var po = [Policy No] var te = [TERM]

var pd = CALCULATE(max(Data[Transaction Date]),ALL(Data),Data[Policy No]=po,Data[TERM]=te,Data[Transaction Date]<md, not Data[Event Code] in {"update_policy"})

var pdv = CALCULATE(sum(Data[Premium]),ALL(Data),Data[Policy No]=po,Data[TERM]=te,Data[Transaction Date]=pd)

return coalesce(pdv,[Premium])

))

Prev Term Renewed Prem =

if([Event Code] in {"policy_renewed"},

var po = [Policy No] var te = [TERM]

var pd = CALCULATE(max(Data[Transaction Date]),ALL(Data),Data[Policy No]=po,Data[TERM]<te,not Data[Event Code] in {"update_policy"})

var pdv = CALCULATE(sum(Data[Premium]),ALL(Data),Data[Policy No]=po,Data[Transaction Date]=pd)

return coalesce(pdv,[Current Term Prem])

))

How to modify the above code to populate the values for only the latest record "policy_renewed" or policy with record "policy_renewed" which has maximum Term for a given policy which is "Active" only.

Sample Data is in the link below

https://www.dropbox.com/scl/fi/49t01pcoxin0atljxoreq/data-Copy.xlsx?rlkey=rt0uviht1eu01x82dq2jiieha&...