Calculate Years of Policy Retention

kfortenberry — Thu, 14 Mar 2024 00:14:21 GMT

I would appreciate any assistance to create a new measure to calculate the years of policy retention by policy number for the following sample data.

For each unique policy number, there may be several transactions entered for a particular policy year, but I would like a total count of each unique effective date for each policy number.

In this sample data, I would expect 2875 to return a count of 3, 3294 would return a count of 4 and 7236 would return a count of 4.

Policy Number	Effective Date	Transaction Amount
2875	2/1/2020	$ 20.00
2875	2/1/2020	$ 30.00
2875	2/1/2020	$ 25.00
2875	2/1/2021	$ 45.00
2875	2/1/2022	$ 50.00
3294	4/15/2021	$ 22.00
3294	4/15/2022	$ 13.00
3294	4/15/2022	$ 72.00
3294	4/15/2022	$ 55.00
3294	4/15/2023	$ 30.00
3294	4/15/2023	$ 45.00
3294	4/15/2024	$ 60.00
7236	2/1/2018	$ 19.00
7236	2/1/2019	$ 43.00
7236	2/1/2019	$ 23.00
7236	2/1/2019	$ 25.00
7236	2/1/2020	$ 40.00
7236	2/1/2020	$ 17.00
7236	2/1/2021	$ 5.00
7236	2/1/2021	$ 64.00

As different policy numbers may have the same effective dates, I cannot simply count distinct effective dates and compare to distinct policy numbers. I am not concerned with gaps in effective dates as if there is a gap the policy number would also change.

I will be utlizing this data to provide average policy retention periods on simple cards for avariety of other statistics (such as state, coverage type & profession).

Re: Calculate Years of Policy Retention

FreemanZ — Thu, 14 Mar 2024 03:20:33 GMT

hi kfortenberry ,

not so sure if i fully get you. you may try to write a measure like:

measure = SUMX( VALUES(data[policy number]), CALCULATE(DISTINCTCOUNT(data[Effective Date])) )

it worked like:

topic Re: Calculate Years of Policy Retention in DAX Commands and Tips

Calculate Years of Policy Retention

Re: Calculate Years of Policy Retention