Calculate Years of Policy Retention

[kfortenberry]

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). 

[FreemanZ · Accepted solution]

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: