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

     

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 )

 

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

Thanks PaulDBrown that did the job.