Customers without purchases in >6 months

[SammyNed]

Hi Team,

Can someone please assist? So i have simplified data as below, a sales table showing the customer_id's and dates when they have ordered from us. I would like to output a table (table 2 below) showing the customers who haven't bought in the preceding 6 months, accumulative tho. So Customer C only purchased in Jan, so in Aug he hasn't purchased in the last 6 months and gets added to the "lost" customer count in Aug, Sep, Oct & Nov. but he orders again in Dec so he is no longer a "lost" customer.  

Thanking you in advance for your assistance

[PaulOlding · Accepted solution]

Hi @SammyNed 

Here's a measure to count the lost customers.

It gets a list of customers in the last 6 months, a list of customers before the last 6 months, then sees who is in the 2nd list and not the 1st.

Lost Customers = 
VAR _CurrentDate = MAX('Date'[Date])
VAR _6MonthsAgo = EOMONTH(_CurrentDate, -6)
VAR _Last6MonthCustomers =
CALCULATETABLE(
    VALUES(Sales[Customer_ID]),
    DATESINPERIOD('Date'[Date], _CurrentDate, -6, MONTH)
)
VAR _BeforeLast6MonthCustomers = 
CALCULATETABLE(
    VALUES(Sales[Customer_ID]),
    REMOVEFILTERS('Date'[Date]),
    'Date'[Date] <= _6MonthsAgo
)
VAR _LostCustomers = EXCEPT(_BeforeLast6MonthCustomers, _Last6MonthCustomers)
RETURN
    COUNTROWS(_LostCustomers)

[CheanSereyvatan]

Thanks Team. It's what i'm looking for. Thanks very much

[Power-CJ]

@PaulOlding 

thank for the nice Solution. It's working really fine.

Now i'working with the measure and if i dont put it into the context of a customer, for example i put the Lost Customer in a card visual, i'll get i other number like in a Table in Context with the Customer Number.

You have an idea, why?

[SammyNed]

Thanks @PaulOlding 

This solution works for what i need. 

[amitchandak]

@SammyNed , with help of the date table, find rolling 6-month sales or the count

example

Rolling 6 = CALCULATE(sum(Sales[Sales Amount]),DATESINPERIOD('Date'[Date ],MAX('Date'[Date ]),-6,MONTH))

Lost customer =

Countx(Values(Customer[Customer]), if(isblank([Rolling 6 ]), [Customer], blank()) )

refer

Customer Retention Part 5: LTD Vs Period Retention
https://community.powerbi.com/t5/Community-Blog/Customer-Retention-Part-5-LTD-and-PeriodYoY-Retentio...

Customer Retention Part 2: Period over Period Retention :https://community.powerbi.com/t5/Community-Blog/Customer-Retention-Part-2-Period-over-Period-Retenti...