Forum Discussion
Lost Customers - Issues with DAX Calculation
I'm having an issue with a DAX calc for lost customers and I'm hoping someone can spot where we went wrong. I have followed the pattern described in http://www.daxpatterns.com/new-and-returning-customers/ to get lost customers, but it is returning unexpected results. I'm hoping there is just a mistake in my formula that I am overlooking. I'm curious if anyone else has tried this and experienced a similar issue.
I have tables for Customer, Date, Sales, which are relevant here. Sales are at the invoice line level, and each line is related to customer by CustomerKey (surrogate key to a type 2 SCD) and date through InvoiceDateKey (surrogate key to date dim). The InvoiceDate field was added as a calculated column to the Sales table.
We are defining lost customers as any customer who hasn't made a purchase in 60 consecutive days ending in the selected timeframe. I have a customer that had purchases on 18-Dec-15, 29-Jan-16, and 12-Feb-16, but is somehow being counted as a lost customer in February. The only thing I noticed about them is that the Dec sales records are related to a different customer key than the Jan and Feb sales because someone corrected the customer name. I don't think this should happen since I'm counting unique [Customer No] values rather than [CustomerKey] values.
Here's my formula:
Lost Customers :=
IF (
NOT (
MIN ( 'Date'[Full Date] )
> CALCULATE ( MAX ( Sales[Invoice Date] ), ALL ( Sales ) )
),
COUNTROWS (
FILTER (
ADDCOLUMNS (
FILTER (
CALCULATETABLE (
ADDCOLUMNS (
VALUES ( Sales[CustomerNO] ),
"CustomerLostDate", CALCULATE ( MAX ( Sales[Invoice Date] ) ) + [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] )
)
)
)
Any ideas where my issue is?
Edit:
Here are the results in a pivot table. Notice the first pivot has only Customer No and seems to work. The second includes customer name and provides unexpected results.
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] )
20 Replies
- MattAllingtonCommunity Champion
I have followed the pattern and it worked for me. The only obvious thing I see is that you have an extract IF statement at the start. Have you tried it without this?
- MeaganMost Valuable Professional
I removed it and got the same results. Thanks for the suggestion, though.
I'm thinking that it has to do with the customer dimension being a type 2 SCD. The Dec sales are on one customer key (surrogate key) and the Jan and Feb sales are on another. I don't really understand how that could affect this since I never use the customer key anywhere, and the customer number (business key) is the same for all the customer keys for that customer.
- MattAllingtonCommunity Champion
So you have 2 IDs for the customer? That sounds suspicious. I think (eg I am not 100% sure but give it a try) that your dim table needs to connect to a primary key for the customer, and then use the same primary key for all your calcs. If I understand you correctly (which I may not be), you are joining on one key (SCD Surrogate) and then doing the calc on a different ID. If this is what you are doing, I am thinking this could be the culprit.