Customers who purchased in Period 1 but NOT in Period 2

[domtrump]

I'm looking for a DAX pattern that will I dentify patrons who made a purchase in the 90 days prior to a specified date but did NOT make a purchase in the 90 days after that date. I've found some stuff in the forum that is similar, but I just can't seem to get it right.

Patrons could have made multiple purchases in the first period, but I'm only interested in identifying the unique customers. I'd want a measure that can provide KPIs (counts, $ spent, etc.), but I also need to FLAG the individual customers (I'm assuming with a calculated field) so that the non-returning customers can be contacted by CRM.

Suggestions? (Note: I just bought the most recent edition of DAX Patterns, so if there is something close in there - point me to it!)

Thanks. -Dom

[BrianConnelly]

Try Something like this

Customers Measure=
VAR comparableDate = SELECTEDVALUE(<date field>, <Alternative>)
VAR firstCustomerList = FILTER(<customerid field>,<date field> <= comparableDate && <date field> >=comparableDate - 90)
VAR secondCustomerList = FILTER(<customerid field>,<date field> > comparableDate && <date field> <=comparableDate + 90)

RETURN CALCULATE(COUNTROWS(firstCustomerList),NOT(secondCustomerList))

[tamerj1]

Hi @domtrump 

you may try something like

Lost Customers =
VAR CurrentDate =
MAX ( 'Date'[Date] )
VAR PreviousCustomers =
CALCULATETABLE (
VALUES ( Sales[CustomerID] ),
'Date'[Date] < CurrentDate,
'Date'[Date] >= CurrentDate - 90,
ALL ( 'Date' )
)
VAR CurrentCustomers =
CALCULATETABLE (
VALUES ( Sales[CustomerID] ),
'Date'[Date] >= CurrentDate,
'Date'[Date] < CurrentDate + 90,
ALL ( 'Date' )
)
RETURN
COUNTROWS ( EXCEPT ( PreviousCustomers, CurrentCustomers ) )

Lost Sales =
VAR CurrentDate =
MAX ( 'Date'[Date] )
VAR PreviousCustomers =
CALCULATETABLE (
VALUES ( Sales[CustomerID] ),
'Date'[Date] < CurrentDate,
'Date'[Date] >= CurrentDate - 90,
ALL ( 'Date' )
)
VAR CurrentCustomers =
CALCULATETABLE (
VALUES ( Sales[CustomerID] ),
'Date'[Date] >= CurrentDate,
'Date'[Date] < CurrentDate + 90,
ALL ( 'Date' )
)
RETURN
CALCULATE ( [Sales Amount], ( EXCEPT ( PreviousCustomers, CurrentCustomers ) ) )