Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Get certified in Microsoft Fabric—for free! For a limited time, the Microsoft Fabric Community team will be offering free DP-600 exam vouchers. Prepare now

Reply
gtrani
Frequent Visitor

Count of new and recurring customers.

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:

example.jpg

 

Any ideas of how to do it?

Tks!!

1 ACCEPTED SOLUTION
v-yanjiang-msft
Community Support
Community Support

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.

vkalyjmsft_0-1667985774305.png

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.

View solution in original post

3 REPLIES 3
v-yanjiang-msft
Community Support
Community Support

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.

vkalyjmsft_0-1667985774305.png

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.

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

djurecicK2
Super User
Super User

This article should give you a good start:

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

 

Helpful resources

Announcements
OCT PBI Update Carousel

Power BI Monthly Update - October 2024

Check out the October 2024 Power BI update to learn about new features.

September Hackathon Carousel

Microsoft Fabric & AI Learning Hackathon

Learn from experts, get hands-on experience, and win awesome prizes.

October NL Carousel

Fabric Community Update - October 2024

Find out what's new and trending in the Fabric Community.