Lost Customers - Issues with DAX Calculation

Hi zkazimov

Try this calculated field formula

=COUNTROWS(FILTER(SUMMARIZE(CALCULATETABLE(VALUES(SALES[CUSTOMER]),DATESBETWEEN('CALENDAR'[Date],EDATE(MIN('CALENDAR'[Date]),-1),max('CALENDAR'[Date]))),[CUSTOMER],"ABCD",[TOTAL SALES],"EFGH",[TOTAL SALES PREV PERIOD]),[EFGH]>0&&ISBLANK([ABCD])))

Hope this helps.

Hi zkazimov,

If my reply helped, please mark it as Answer.

Thanks so much, Marco. That did the trick. For anyone else who happens to need it, here is my full DAX calculation: 

Lost Customers:=IF (
    NOT (
        MIN ( 'Date'[Full Date] )
            > CALCULATE ( MAX ( Sales[Invoice Date] ), ALL ( Sales ) )
    ),
    COUNTROWS (
        FILTER (
            ADDCOLUMNS (
                FILTER (
                    CALCULATETABLE (
                        ADDCOLUMNS (
                            CALCULATETABLE ( VALUES ( Customer[Customer No] ), Sales ),
                            "CustomerLostDate", CALCULATE (
                                MAX ( Sales[Invoice Date] ),
                                ALLEXCEPT ( Customer, Customer[Customer No] )
                            )
                                + [Lost Days Limit]
                        ),
                        FILTER (
                            ALL ( 'Date' ),
                            AND (
                                'Date'[Full Date] < MIN ( 'Date'[Full Date] ),
                                'Date'[Full Date]
                                    >= MIN ( 'Date'[Full Date] ) - [Lost Days Limit]
                            )
                        )
                    ),
                    AND (
                        AND (
                            [CustomerLostDate] >= MIN ( 'Date'[Full Date] ),
                            [CustomerLostDate] <= MAX ( 'Date'[Full Date] )
                        ),
                        [CustomerLostDate] <= CALCULATE ( MAX ( Sales[Invoice Date] ), ALL ( Sales ) )
                    )
                ),
                "FirstBuyInPeriod", CALCULATE ( MIN ( Sales[Invoice Date] ) )
            ),
            OR ( ISBLANK ( [FirstBuyInPeriod] ), [FirstBuyInPeriod] > [CustomerLostDate] )
        )
    )
)

Hi Guys,

I need help understanding how to find lost customers. All I need is to find out customers who had sales last period, but not currnet period depending on the relative date filter selected. I don't understand concept used with "Lost Days Limit". 

I have read  http://www.daxpatterns.com/new-and-returning-customers/ multiple times.  I have no issues with Return and New Customers Concept. 

Thank you. Appreciated. 

Hi zkazimov,

Share a dataset and show the expected result.

Hi Ashish_Mathur,

I want lost customers to be like in the image below for new customers. Links below for sample file and data source.

https://1drv.ms/u/s!ArZK-htpGmgl7iruP47UKkZYs0yR

Hi zkazimov,

To keep things simple, try this calculated field formula

=if(AND([TOTAL SALES PREV PERIOD]>0,[TOTAL SALES]=0),1,0)

In the Visual level filters section, apply a criteria of 1.

Hope this helps.

Hi Ashish_Mathur

That works as a workaround, but not able to aggregate that measure to show on for example usign Card Visual. Also in the table see below how it is not aggreagted for total lost customers.

I need same effect as i have in the table for new customers.

Ashish_Mathur

This is great! Summarize Function did the job. Thanks a lot for time spent on this Appreciated. 

I am having same issue, could you please share the DAX you created ?

This is a great calc. The problem Im having is that its counting a customer as lost that didnt buy for 12 consecutive months more than once in their 5 year history. Only the last 12 months with no sales shoudl be counted as lost. How do I get it to only grab the last purchase date.  Wehn I wabt to displayit by month it shows as lost in prior years. thanks

There is a new version of the pattern that considers new, lost, and recovered customers - it is more flexible and you should be able to adapt it to your requirements:

New and returning customers – DAX Patterns