how to calculate lost customer distinct count on month level using dax measure in power pivot?

[Anonymous]

Hello everyone,

I hope you're all doing well. I'm currently working on a project and could use some guidance regarding a DAX measure. I'm trying to calculate distinct counts for different types of customers on a monthly basis, and I'm encountering a few challenges.

Specifically, I'm aiming to determine the following metrics using DAX measures:

1. Lost Customer Distinct Count: The count of customers who were active in the previous month but haven't made any transactions in the current month.
2. New Customer Distinct Count: The count of customers who are making their first transaction in the current month.
3. Repeat Customer Distinct Count: The count of customers who have made transactions in both the current month and the previous month.

The database has been loaded into Power Query, and I've successfully established connections in Power Pivot.

Additionally, I'm hoping to enhance this analysis by segmenting the results based on categories and brands, if possible.

If any of you have experience with DAX measures and are willing to lend a helping hand, I would greatly appreciate it. Your expertise could make a significant difference in advancing this project.

Thank you in advance for your support. Looking forward to your insights!

Best regards, Santosh

Sample database table for reference.

date	customer id
01 January 2022	cust-001
02 January 2022	cust-002
03 January 2022	cust-003
01 January 2022	cust-001
01 February 2022	cust-001
02 February 2022	cust-002
03 February 2022	cust-004
02 February 2022	cust-002
01 March 2022	cust-001
01 March 2022	cust-002
01 March 2022	cust-005
01 March 2022	cust-002

[Anonymous]

Thank you so very much for providing me the dax. It works fine on date level when it is being analysed on month level in pivot table the result reflects as zero. Kindly have a look once more. Thanks! once again. 

[johnt75]

Try adding REMOVEFILTERS('Date') into the CALCULATETABLE calls.

[johnt75]

You could create measures like

Lost Customers =
VAR CurrentCustomers =
    DISTINCT ( 'Table'[customer id] )
VAR PrevCustomers =
    CALCULATETABLE (
        DISTINCT ( 'Table'[customer id] ),
        DATEADD ( 'Date'[Date], -1, MONTH )
    )
VAR Result =
    COUNTROWS ( EXCEPT ( PrevCustomers, CurrentCustomers ) )
RETURN
    Result

New Customers =
VAR CurrentCustomers =
    DISTINCT ( 'Table'[customer id] )
VAR PrevCustomers =
    CALCULATETABLE (
        DISTINCT ( 'Table'[customer id] ),
        DATEADD ( 'Date'[Date], -1, MONTH )
    )
VAR Result =
    COUNTROWS ( EXCEPT ( CurrentCustomers, PrevCustomers ) )
RETURN
    Result

Repeat Customers =
VAR CurrentCustomers =
    DISTINCT ( 'Table'[customer id] )
VAR PrevCustomers =
    CALCULATETABLE (
        DISTINCT ( 'Table'[customer id] ),
        DATEADD ( 'Date'[Date], -1, MONTH )
    )
VAR Result =
    COUNTROWS ( INTERSECT ( CurrentCustomers, PrevCustomers ) )
RETURN
    Result