Count of new and recurring customers.

[gtrani]

Hi!

I have the dataset attached, and want to have the following output:

dataset: https://docs.google.com/spreadsheets/d/1Lm-nf0llQIowqycl2Ls5qQflTiBlTw2V/edit?usp=share_link&ouid=10... 

monthly, the total quantity of customers (id-cliente), categorized in:

• quantity of new customers (never purchased before);
• quantity of customers who purchased in the past month;
• quantity of customers who purchased before the past month.

Example of the desired output:

Any ideas of how to do it?

Tks!!

[v-yanjiang-msft · Accepted solution]

Hi @gtrani ,

According to your description, I download your dataset and here's my solution.

Create two calculated columns.

YearMonth =
YEAR ( [Data] ) & "-"
    & IF ( LEN ( MONTH ( [Data] ) ) = 1, 0 & "" & MONTH ( [Data] ), MONTH ( [Data] ) )

Category =
VAR _New =
    COUNTROWS (
        FILTER (
            'Planilha1',
            'Planilha1'[id-cliente] = EARLIER ( 'Planilha1'[id-cliente] )
                && 'Planilha1'[YearMonth] < EARLIER ( 'Planilha1'[YearMonth] )
        )
    )
VAR _LastM =
    COUNTROWS (
        FILTER (
            'Planilha1',
            'Planilha1'[id-cliente] = EARLIER ( 'Planilha1'[id-cliente] )
                && EOMONTH ( 'Planilha1'[Data], 0 ) = EOMONTH ( EARLIER ( Planilha1[Data] ), -1 )
        )
    )
RETURN
    IF ( _New = 0, "New", IF ( _LastM > 0, "LastM", "Before" ) )

Put YearMonth column in X-axis, Category in Y-axis and Legend, Get the result.

I attach my sample below for your reference.

Best Regards,
Community Support Team _ kalyj

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[gtrani]

@v-yanjiang-msft this really solved my problem!! Thank you so much!

[djurecic]

This article should give you a good start:

https://www.daxpatterns.com/new-and-returning-customers/