Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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.
Solved! Go to Solution.
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] )
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
110 | |
94 | |
82 | |
66 | |
58 |
User | Count |
---|---|
151 | |
121 | |
104 | |
87 | |
67 |