topic Customers without purchases in >6 months in DAX Commands and Tips

Customers without purchases in >6 months

SammyNed — Tue, 09 Nov 2021 06:59:51 GMT

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

Re: Customers without purchases in >6 months

amitchandak — Tue, 09 Nov 2021 08:56:13 GMT

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-Retention-is-only/ba-p/2114497

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

Re: Customers without purchases in >6 months

PaulOlding — Tue, 09 Nov 2021 15:45:40 GMT

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)

Re: Customers without purchases in >6 months

SammyNed — Wed, 10 Nov 2021 14:52:00 GMT

Thanks PaulOlding

This solution works for what i need.

Re: Customers without purchases in >6 months

Power-CJ — Thu, 19 Jan 2023 15:55:37 GMT

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?

Re: Customers without purchases in >6 months

CheanSereyvatan — Fri, 11 Aug 2023 07:23:51 GMT

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