New and repeat customer with rules in PBI

[sini]

I would like to create visualization for new and repeat customer based on these rules

We have customer group and customer. For example,

Customer group	Customer	Order date
A	x	1/1/2024
A	y	1/2/2024
B	z	1/1/2024

New customers = customers or customer groups that haven't ordered in the past

Repeat customers = customers or customer groups that ordered again

From the example table,

January 2024

New customers = 2 (A-x and B-z)

Repeat customers = 0

February 2024

New customers = 0

Repeat customers = 1 (A-y)

Could you kindly guide dax to fullfil the above rules? 🙂

[Anonymous]

Hi @sini ,

I created a sample file, hope that helps:

Output:

Best Regards,
Gao
Community Support Team

If there is any post helps, then please consider Accept it as the solution  to help the other members find it more quickly.
If I misunderstand your needs or you still have problems on it, please feel free to let us know. Thanks a lot!

How to get your questions answered quickly --  How to provide sample data in the Power BI Forum

[sini]

Wow, this works perfectly.

Can I add more condition for the customer hierarchy?

There're customers without customer group.

Customer group	Customer	Order date
	m	1/1/2024
	n	1/1/2024
	m	1/2/2024

From the example table,

January 2024

New customers = 2 (m and n)

Repeat customers = 0

February 2024

New customers = 0

Repeat customers = 1 (m)

Could you kindly add this rule to the measures?😊

[powerbidev123]

Hi @sini ,

Try these

NewCustomers =

CALCULATE(

    DISTINCTCOUNT('Table (3)'[Customer]),

    FILTER(

        'Table (3)',

        FORMAT('Table (3)'[Order Date], "YYYY-MM") = FORMAT(MINX(FILTER('Table (3)','Table (3)'[Customer]= EARLIER('Table (3)'[Customer])), 'Table (3)'[Order Date]), "YYYY-MM")

    )

)

RepeatCustomers =

CALCULATE(

    DISTINCTCOUNT('Table (3)'[Customer]),

    FILTER(

        'Table (3)',

        FORMAT('Table (3)'[Order Date], "YYYY-MM") >

        FORMAT(MINX(FILTER('Table (3)', 'Table (3)'[Customer] = EARLIER('Table (3)'[Customer])), 'Table (3)'[Order Date]), "YYYY-MM")

    )

)

[sini]

Hi,

Thank you for the measures, but they didn't consider the group level of the customer?