Help in Identifying Renewal Customers

[James__]

Hi,

I have a list of policy sales and i need to identify which customers have renewed. Each policy is 12 months in duration and each customer has an unique ID.

Can anyone help identify which policy have been renewed? I'd like to sum this data and the turn it into a percentage, which is the easy bit. I'm just struggling to write the measure to idenify renewed customers.

Sample data:

[James__]

Unique ID	Reg	Policy Count	Product	Inception Date	Is_renewal (1,0)
ABC123	NU72CAR	1	MOT Cover	01/01/2021	0
ABC124	NU72CAB	1	MOT Cover	01/01/2022	0
ABC125	NU72CAC	1	MOT Cover	01/01/2022	0
ABC123	NU72CAR	1	MOT Cover	01/01/2022	1

As you can see, unique user ABC123 has renewed their product. Initial product has an inception date of 01/01/2021 and the renewal product has an inception date of 01/01/2022. The vehicle reg is the same, which is important. 

[PaulDBrown · Accepted solution]

Try this measure:

Renewed =
VAR _ID =
    MAX ( 'Table'[Unique ID] )
VAR _Reg =
    MAX ( 'Table'[Reg] )
VAR _Renewed =
    IF (
        COUNTROWS (
            SUMMARIZE (
                FILTER ( ALL ( 'Table' ), 'Table'[Unique ID] = _ID && 'Table'[Reg] = _Reg ),
                'Table'[Unique ID],
                'Table'[Reg],
                'Table'[Inception Date]
            )
        ) > 1,
        "Renewed"
    )
VAR _date =
    CALCULATE (
        MAX ( 'Table'[Inception Date] ),
        FILTER ( ALLEXCEPT ( 'Table', 'Table'[Unique ID] ), NOT ISBLANK ( _Renewed ) )
    )
RETURN
    IF ( MAX ( 'Table'[Inception Date] ) = _date, _date )

[James__]

Thanks @PaulDBrown that did the job.

[amitchandak]

@James__ ,
Can you please share sample data and sample output in table format?