Old vs new customer calculated column

[Giorgi1989]

Hi,

I am trying to create a calculated  column that will consist of two options: 'Old' or 'New customer', based on certain crtieria, which I will provide below:  

• If the customer has sales within last 12 months from the last purchase AND has no sales prior to that, then the customer is 'New'. 
• If on the othe hand, the customer has sales both within last 12 months as well as months prior to that, then the customer is 'Old'. 

It seems to work fine for the last 12 months (From Sep 2023). But, then some months appear blank, which is not plausible. 

Please, see my DAX below. I have been brainstorming for quite some time, what might be the reason behind the blank values in that simple matrix above.

The sales table has the data of about 5 years in it. 

Customer Type =

VAR MAXMonth =

          MAX('Calendar'[date])

VAR Last12Months =

    DATESINPERIOD('Calendar'[date], MAXMonth,-12,MONTH)

VAR Last13to36Months =

    DATESINPERIOD(''Calendar'[date],Date(Year(MAXMonth)-1,Month(MAXMonth), Day(MAXMonth)),-24,MONTH)

RETURN

IF(

    COUNTROWS (

        FILTER (

            'Sales',

            'Sales'[Customer] = EARLIER('Sales'[Customer])

            && 'Calendar'[date] IN Last13to36Months

        )

    ) > 0

    && COUNTROWS (

        FILTER (

            'Sales',

            'Sales'[Customer] = EARLIER('Sales'[Customer])

            && 'Calendar'[date] IN Last12Months

        )

    ) > 0,

    "Repeat customer",

    "Old customer"

)

Anyone has an idea what might be wrong? Any inaccuracies in the DAX above, which I am simply overlooking?

Thank you for your help! 

[Giorgi1989]

Thank you, but that would not help! 

[BeaBF]

@Giorgi1989  Hi! I'va adjusted the code:

Customer Type =
VAR MAXMonth = MAX('Calendar'[date])
VAR Last12Months = DATESINPERIOD('Calendar'[date], MAXMonth, -12, MONTH)
VAR Last13to36Months = DATESBETWEEN('Calendar'[date], DATE(YEAR(MAXMonth) - 3, MONTH(MAXMonth), DAY(MAXMonth)), DATE(YEAR(MAXMonth) - 1, MONTH(MAXMonth), DAY(MAXMonth)))
RETURN
IF (
COUNTROWS (
FILTER (
'Sales',
'Sales'[Customer] = EARLIER('Sales'[Customer])
&& 'Calendar'[date] IN Last13to36Months
)
) > 0
&& COUNTROWS (
FILTER (
'Sales',
'Sales'[Customer] = EARLIER('Sales'[Customer])
&& 'Calendar'[date] IN Last12Months
)
) > 0,
"Old customer",
"New customer"
)

BBF