Forum Discussion

James__'s avatar
James__
Icon for Helper I rankHelper I
3 years ago
Solved

Help in Identifying Renewal Customers

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:

 

 

Need Help
  • PaulDBrown's avatar
    PaulDBrown
    3 years ago

    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 )

     

4 Replies

  • James__'s avatar
    James__
    Icon for Helper I rankHelper I
    3 years ago
    Unique IDRegPolicy CountProductInception DateIs_renewal (1,0)   
    ABC123NU72CAR1MOT Cover01/01/20210   
    ABC124NU72CAB1MOT Cover01/01/20220   
    ABC125NU72CAC1MOT Cover01/01/20220   
    ABC123NU72CAR1MOT Cover01/01/20221   
             
             
             
             
             

     

    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's avatar
      PaulDBrown
      Icon for Community Champion rankCommunity Champion
      3 years ago

      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 )