Check if an entry is already existing in the table in the given period

[mba]

Hi all,

Let's say the table I desire to have looks more or less like this:

Having been given, for example, the register date and a customer, I want to see if the customer has been already registered in the last 3 months. Return "Existing" if so, "New" otherwise. How can I achieve that?

Register Date	Customer	IsNewCustomer
12/01/2020	A	New
20/02/2020	A	Existing
22/03/2021	B	New
25/05/2021	B	Existing
11/04/2022	B	New
10/02/2020	C	New
24/03/2022	C	New

[TomMartens · Accepted solution]

Hey @mba ,

I use the below DAX to compute a calculated column:

New Or Existing Customer = 
var currentDate = 'Table'[Register Date]
var currentCustomer = 'Table'[Customer]
var NoOfMonth =
DATEDIFF(
    MAXX( 
        TOPN(
            1
            , FILTER( 'Table' , 'Table'[Customer] = currentCustomer && 'Table'[Register Date] < currentDate )
            , 'Table'[Register Date] 
            , DESC
        )
        , 'Table'[Register Date]
    )
    , currentDate
    ,MONTH
)
var result = NoOfMonth
return
IF( ISBLANK( result ) || result > 3 , "New" , "Existing" )

Based on the sample data you provided it looks like it creates the expected result:

Please check the condition that determines if it's a "New" or an "Exiting" customer, sometimes I have difficulties with this kind of condition.

Nevertheless, I hope this provides what you are looking for.

Regards,

Tom