New Customers / Lost Customers

[AliB]

Having watched the video for New Customer / Returning / Lost Customers I have the following

On the Lost Customers for the 1st 3mths the data is incorrect as it seems to follow the profile of new customers is there a way to suppress this?

As my data is based on months ie 1 entry per customer per month dated the last day of the month is there a way to change this so it considers the customer to be lost after 3 months rather than 90 days
Customer Attrition Test.pbix (477.0 KB)
NB I’ve changed the data to be the last of the month rather than the 1st of the month

[Harsh]

Hello @AliB,

Thank You for posting your query onto the Forum.

Since you want to consider the metric or criteria as months rather than days this type of scenario is covered under the DAX Patterns article of SQLBI. So below is the measure provided for the reference of the “Lost Customers” i.e. Customer is considered as lost after 3 months and not after 90 days.

Date Lost Customer = 
CALCULATE (
    EOMONTH ( MAX ( SalesData[SalesDate] ), 3 ),
    REMOVEFILTERS ( Dates )
)




# Lost Customers = 
VAR LastDateLost =
    CALCULATE (
        MAX ( Dates[Date] ),
        ALLSELECTED ( Dates )
    )
VAR CustomersWithLostDate =
    CALCULATETABLE (
        ADDCOLUMNS (
            VALUES ( SalesData[Group] ),
            "@LostCustomerDate", [Date Lost Customer]
        ),
        ALLSELECTED ( Customers ),
        Dates[Date] <= LastDateLost
    )
VAR LostCustomers =
    FILTER (                              
        CustomersWithLostDate,
        [@LostCustomerDate]
            IN VALUES ( Dates[Date] )
    )
VAR Result =
    COUNTROWS ( LostCustomers )
                                                         
RETURN
    Result

Below is the screenshot of the final results provided for the reference -

I’m also attaching the working of the PBIX file as well of the link of the DAX Patterns article on Customer Attrition Analysis for the reference purposes from where you can also download the reference PBIX files.

Please go through that blog or article where different scenarios are explained and measures are provided accordingly. Please modify the measures as per your business requirements.

Hoping you find this useful and meets your requirements that you’ve been looking for.

Thanks and Warm Regards,
Harsh

Customer Attrition Test - Harsh.pbix (382.6 KB)

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

[AliB]

Thanks Harsh for taking the time to help - that works great

Is there a way to return a table of the customer names that are considered lost in any month and the value that’s been lost based on their last 12 mths sales from the date they are considered last?

[Harsh]

Hello @AliB,

To find the names of the “Lost Customers”. Below is the measure provided for the reference -

Lost Customers - Names = 
VAR LastDateLost =
    CALCULATE (
        MAX ( Dates[Date] ),
        ALLSELECTED ( Dates )
    )
VAR CustomersWithLostDate =
    CALCULATETABLE (
        ADDCOLUMNS (
            VALUES ( Customers[Customer] ),
            "@LostCustomerDate", [Date Lost Customer]
        ),
        ALLSELECTED ( Customers ),
        Dates[Date] <= LastDateLost
    )
VAR LostCustomers =
    FILTER (                              
        CustomersWithLostDate,
        [@LostCustomerDate]
            IN VALUES ( Dates[Date] )
    )
VAR NoOfLostCustomers =
    COUNTROWS ( LostCustomers )
                                                         

VAR NamesOfCustomersLost =
CONCATENATEX(
    LostCustomers , 
    Customers[Customer] , 
    ", " )

VAR Results = 
IF( HASONEVALUE( Dates[MonthInCalendar] ) ,
    NamesOfCustomersLost , 
    BLANK() )


RETURN
    Results

And then to calculate the “Lost Customers Sales” below is the measure provided for the reference -

Sales Lost Customers = 
VAR LastDateLost =
    CALCULATE (
        MAX ( Dates[Date] ),
        ALLSELECTED ( Dates )
    )
VAR CustomersWithLostDate =
    CALCULATETABLE (
        ADDCOLUMNS (
            VALUES ( SalesData[Group] ),
            "@LostCustomerDate", [Date Lost Customer]
        ),
        ALLSELECTED ( Customers ),
        Dates[Date] <= LastDateLost
    )
VAR LostCustomers =
    FILTER (
        CustomersWithLostDate,
        [@LostCustomerDate]
            IN VALUES ( Dates[Date] )
    )
VAR Previous12Months = 
    DATESINPERIOD (
        Dates[Date],
        EOMONTH ( MAX ( Dates[Date] ), -3 ), 
        -12, 
        MONTH 
    )
VAR Result =
    CALCULATE (
        [Total Sales],
        Previous12Months,
        KEEPFILTERS ( LostCustomers )
    )
RETURN
    Result

Now, after writing these 2 measures you’ll be able to see the results at a consolidated as well as at an individual level i.e. The Names of the Customers that are lost as well as the Lost Sales Value against those Customers. Below is the screenshot of the final results provided for the reference -

I’m also attaching the working of the PBIX file for the reference purposes.

Hoping you find this useful and meets your requirements that you’ve been looking for.

Thanks and Warm Regards,
Harsh

Customer Attrition Test - Harsh v2.pbix (388.3 KB)

[AliB]

This is great Harsh - thank you.

Just one change to make if possible or if you could explain how I can make the change please

The sales for Lost Customers needs to be their last 12 actual invoices
I’m guessing I need to change this part
VAR Previous12Months =
DATESINPERIOD (
Dates[Date],
EOMONTH ( MAX ( Dates[Date] ), -3 ),
-12,
MONTH
)

I changed it to 0 but that didn’t make the correct change

[Harsh]

Hello @AliB,

Please create a new thread for the adjacent or tangential queries, if your original query has been answered and have you gone through the link of the DAX Patterns which I had provided it also contains the PBIX files with measures created for different scenarios.

Thanks and Warm Regards,
Harsh

[Rich]

Thank you for all of this. How would we calculate based on days instead of end of month for “Date Lost Customer” query? I basically need it say 92 days instead of 3 months.

My other question is when I do use the EOMONTH function in date of month, it is showing information 3 months into the future. For example, even though today is 3/1/22, it is showing lost customers in April / May / June. I just want it to work from today to the past.

[Paul.Gerber]

@Rich I watched the same training video. I used it in my report. Maybe change the day values. Instead of 60 use 92 for the MIN date?

Lost Customers =
VAR vCustomersPurchased = CALCULATETABLE(VALUES(‘Table’[Name] ),
FILTER( ALL(‘Date’ ),
‘Date’[Date] > MIN(‘Date’[Date] ) - 365 &&
‘Date’[Date] < MIN(‘Date’[Date] ) - 60 ))

VAR vPriorCustomers = CALCULATETABLE(VALUES(‘Table’[Name] ),
FILTER( ALL(‘Date’ ),
‘Date’[Date] > MIN(‘Date’[Date] ) - 60 &&
‘Date’[Date] < MIN(‘Date’[Date] ) ) )
RETURN
COUNTROWS( EXCEPT( vCustomersPurchased, vPriorCustomers )) * -1

[Rich]

@Paul.Gerber

Thanks for the response. I was actually referencing the PBIX file that Harsh had created for the original poster. His formulas are much different and seem to work better for my use since it can list names of lost customers. For whatever reason (I’m not sure), several of those formulas from the video do not work for my dataset and I never could figure out the problem but @Harsh solution works for me.

[Rich]

I think I figured it out.

Date Lost Customer =
CALCULATE ( – The last sale occurs three months after
EOMONTH( MAX ( Sales[InvoiceDate] )-45 , 3 ), – the last transaction (end of month)
REMOVEFILTERS ( Dates ) – at any time

Basically I wanted to say it was a lost sale after 45 days. Added that -45 after the max function and it seems to be giving me the result I wanted.