How identify Repeat customers useing dax formula

[lavdeepk]

Hi All,

I need your help to identify ( Create calculated column ) Repeat customers based on the previous year's data using the DAX formula. For example, if FY -21 customers exist in FY 20 then they should be marked, Repeat customers. sharing below sample data along with desired output.

FY	Customer Id	Collection Value	Status (desired output.)
FY-20	CUST-1050205	184593
FY-20	CUST-1050588	131330
FY-20	CUST-1025818	108236
FY-20	CUST-829412	128024
FY-20	CUST-1052190	150436
FY-20	CUST-1049227	139166
FY-21	CUST-1050588	101293	Repeat customers
FY-21	CUST/726341	151588	New customers
FY-21	CUST/3129337	119089	New customers
FY-21	CUST-582722	131873	New customers
FY-21	CUST-556614	190449	New customers
FY-21	CUST-829412	112782	Repeat customers

[CNENFRNL · Accepted solution]

Result = 
IF(
    ISEMPTY(
        FILTER(
            CRM,
            CRM[Customer Id] = EARLIER( CRM[Customer Id] )
                && CRM[Index] < EARLIER( CRM[Index] )
        )
    ),
    "New",
    "Repeat"
)

[Jihwan_Kim · Accepted solution]

Status CC =
VAR currentFY =
INT ( RIGHT ( 'Table'[FY], 2 ) )
RETURN
IF (
'Table'[Customer Id]
IN SELECTCOLUMNS (
FILTER ( 'Table', INT ( RIGHT ( 'Table'[FY], 2 ) ) < currentFY ),
"@customerID", 'Table'[Customer Id]
),
"Repeat customers",
"New customers"
)

https://www.dropbox.com/s/sceqafezp50byap/lavdeepk.pbix?dl=0 

[lavdeepk]

@Jihwan_Kim @CNENFRNL 

Both solutions working fine for me. Thanks for great help

Thanks

Lavdeep

[CNENFRNL · Accepted solution]

Result = 
IF(
    ISEMPTY(
        FILTER(
            CRM,
            CRM[Customer Id] = EARLIER( CRM[Customer Id] )
                && CRM[Index] < EARLIER( CRM[Index] )
        )
    ),
    "New",
    "Repeat"
)