Percent Retention

Question

I am trying to create a "Percent Retention" for policies during a given time period ( By month, YTD and year over year) . So all of the policies at a given time period compared to those active at the end of the period.

Policies can be:

    N=New
    RN=ReNew
    C=Cancel
    RI=ReInstate
    NR=NonRenew

Transaction data kinda looks like this, the StatusNum is something I can derive to show inforce status.

PolicyID PolicyNum StatusDate Status StatusNum Net 
1        123       1/1/2018   N         1      1
2        123       3/31/2018  C         0      -1
3        123       4/1/2018   RI        1      +1
4        123       6/1/2018   RN        1      0
5        222       2/1/2018   N         1      1
6        222       7/1/2018   RN        1      0
7        333       1/1/2018   N         1      1
8        333       6/1/2018   NR        0     -1
9        444       1/1/2018   N         1      1
10       444       5/30/2018  C         0     -1

My best guess on how to do this is to take the sum of the last StatusNum values at a PIT (partitioned by Policy Number) divided by the first StatusNum value at the beginning PIT. So if I filter by dates 1/1/2018 to 8/1/2018

123 will be in force (+1,+1)
222 will not be in force yet(so not counted for anything) (+0,+0)
333 was in force at the beginning, but it non renewed (+1,-1)
444 was in force at the beginning, but it cancelled (+1,-1)

So 3 of the policies were active at 1/1/2018 and 2 cancelled, 1 doesn't matter so the retention would be 33.3%

Can anyone offer feedback if this is the best way to do this and how to accomplish this?

Thank you in advance for your assistance.

Update

This is kinda what I am looking for, but it is too slow:

'AsOfPolicies = var A= SELECTCOLUMNS(SUMMARIZECOLUMNS(Transactions[PolicyNumber], filter( Transactions,Transactions[DateKey]=min(Transactions[DateKey])&&Transactions[IsInForce]=-1) ),"aPolicyNumber", [PolicyNumber])
var B=SELECTCOLUMNS(SUMMARIZECOLUMNS(Transactions[PolicyNumber], filter( Transactions,Transactions[DateKey]<=MAX(Transactions[DateKey]) ),"MaxDate",MAX(Transactions[DateKey]) ),"bPolicyNumber",[PolicyNumber],"MaxDate",[MaxDate]) var C = SELECTCOLUMNS(filter(CROSSJOIN(A,B),[aPolicyNumber]=[bPolicyNumber]),"cPolicyNumber",[aPolicyNumber],"MaxDateKey",[MaxDate]) Var D = SELECTCOLUMNS(filter(CROSSJOIN(C,Transactions),[cPolicyNumber]=[PolicyNumber] && [MaxDateKey]=[DateKey]),"PolicyNumber",[PolicyNumber],"PD_ID",[PD_ID],"IsInForce",[IsInForce]) Return D'

Update

Also the filter does not appear to be working

Why is 222 not in force yet? 8/1/2018 is certainly after 2/1/2018 and 7/1/2018. — Alexis Olson
This question reminds me of this one. You may be able to use similar logic. — Alexis Olson
222 is not in force on 1/1/2018, therefore it would not be counted in the numerator or denominator. Only policies in force on 1/1/2018. Great find for the other solution! I'll see if I can get it working and report back. Thanks! — Paul
I wrote the other solution, so it was in the back of my mind. I'm still not positive I'm following the logic completely. The 33% retention is for policies started by 1/1 and looking at how many are active at 8/1 and 222 would be picked up if you started your date filter at 2/1 instead of 1/1? — Alexis Olson
That is completely correct! Basically all "active" policies at the point in time and of those, what percent are still active at the end. It shouldn't include and policies were canceled before the pit, then reinstate after the pit or include any new business after the initial pit. Thank you again for your help. Your wisdom is greatly appreciated. — Paul

Alexis Olson Alexis Olson · Accepted Answer · 2018-10-19T15:38:54

I think you can do something like this:

Retention =
VAR StartDates =
    SUMMARIZE (
        ALLSELECTED ( PolicyLog ),
        PolicyLog[PolicyNum],
        "Start", MIN ( PolicyLog[StatusDate] )
    )
VAR Included =
    SELECTCOLUMNS (
        FILTER ( StartDates, [Start] <= MIN ( Dates[Date] ) ),
        "Policies", PolicyLog[PolicyNum]
    )
VAR Filtered = FILTER ( PolicyLog, PolicyLog[PolicyNum] IN Included )

RETURN
    DIVIDE (
        SUMX ( Filtered, PolicyLog[Net] ),
        COUNTROWS ( SUMMARIZE ( Filtered, PolicyLog[PolicyNum] ) )
    )

First, you create a table, StartDates, that gives the earliest dates for each policy limited to the time frame you have selected. It would look something like this:

StartData = 

PolicyNum  Start
123        1/1/2018
222        2/1/2018
333        1/1/2018
444        1/1/2018

From there, we just want a list of which policies we want to include in the calculation. So we pick the ones that have a Start on the minimum selected date in the date slicer. We just want a list of the resulting policy numbers, so we just select that column.

Included =

Policies
123
333
444

From there we filter the whole PolicyLog table to just include these ones (Filtered).

Finally, we can add up the Net column for each of these selected policies and divide by the distinct count of them to get our retention percentage.

Edit: In response to your comment, I think you want to be a bit more selective with the StartDate variable. Instead of MIN( PolicyLog[StatusDate] ), try something more like this:

CALCULATE( MIN(PolicyLog[StatusDate]), PolicyLog[Status] IN {"N", "RN", "RI"} )

Percent Retention

1 Answers