Forum Discussion
How identify Repeat customers useing dax formula
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 |
Result = IF( ISEMPTY( FILTER( CRM, CRM[Customer Id] = EARLIER( CRM[Customer Id] ) && CRM[Index] < EARLIER( CRM[Index] ) ) ), "New", "Repeat" )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
3 Replies
CNENFRNLCommunity Champion
Result = IF( ISEMPTY( FILTER( CRM, CRM[Customer Id] = EARLIER( CRM[Customer Id] ) && CRM[Index] < EARLIER( CRM[Index] ) ) ), "New", "Repeat" )- Jihwan_Kim
Super User
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