cancel
Showing results for
Did you mean:

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Frequent Visitor

## Count of new and recurring customers.

Hi!

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

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!!

1 ACCEPTED SOLUTION
Community Support

Hi @gtrani ,

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.

3 REPLIES 3
Community Support

Hi @gtrani ,

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.

Frequent Visitor

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

Super User

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