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] )
Thank you, Marco. That gives me the correct answer if I only have the Customer No in a pivot table. If I include the Customer Name in the pivot table (this is the attribute that changed over time), it counts them as lost. Is there a way to alter the calculation to change this behavior so I get consistent results in both pivots?
For example, I have a customer who bought something in December and then their name changed, then they had purchases in January and February.
Customer CustomerKey Customer No Customer Name EffectiveStart EffectiveEnd RowIsCurrent 1 1 Person A 1/1/1900 1 2/31/2015 0 2 1 Person A2 1/1/2016 1
SalesInvoice InvoiceDateKey InvoiceDate CustomerKey ProductKey SalesAmount 20151218 12/18/2015 1 3 10 20160129 1/29/2016 2 4 15 20160216 2/12/2016 2 5 20
Their pivot with just Customer No looks good (they are not counted as lost). But if I include Customer Name after the Customer Number, they are counted as lost in February, despite the fact that I am not using the CustomerKey (surrogate key) anywhere in the formula.
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]
)