I'm trying to do a business retention analysis, in this case on auto insurance policies.  I've had help and made some progress, but I never seem to be able to get the final desired report.

My data set is simple:  Policy ID, Date the Policy Started, and Cancel Date (if not active)/Expiration Date (if still active).

It's easy enough to look at policies written 3 years ago and see what % are still active today, but I want to see what % were active after 1 month, after 2 months, ..., after 3 years.

Also a policy written, say 3 months ago, I don't want it included when determining what % are active in 3 years.  Of course it's not been active for 3 years because that's impossible.

So attached is what I have so far.  I have 1 viz showing % of policies still active after every 30 days.  The other viz shows how many days it could have possibly be active. For example, if a policy was written 3 years ago, but cancelled after 2 months, it COULD have been active for 3 years.

Basically the number I'm looking for is the bin value in viz 1 divided by the bin value in viz 2.  For example, For 150 days, 49/86 for 57%.

I'm not married to using bins for this.  I'm not even married to the data set I'm using if I re-pull it a different way.  In my original data set, what I don't have easily available  is a list of active policies each month.  So that solution is not an option.

Side note:  I can't get 0-30 days to populate with 100 on the Days Possible viz because I don't have any policies written <30 days ago so it's not considering the starting point a missing value.

Thanks in advance for your help.