Forum Discussion
Lost Customers - Issues with DAX Calculation
Good point - you have to remove some filter from the CustomerLostDate column expression.
Try this one replacing the ADDCOLUMNS in original formula:
ADDCOLUMNS ( CALCULATETABLE ( VALUES ( Customer[CustomerCode] ), Sales ), "CustomerLostDate", CALCULATE ( MAX ( Sales[Invoice Date] ), ALLEXCEPT ( Customer, Customer[CustomerCode] ) ) + [Lost Days Limit] )
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
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 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.
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.
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 ?
