How to calculate Lost Customer per month with additional condition?

[Anonymous]

Hey all,

I've created a measure for Lost Customers following the tutorial here: https://www.youtube.com/watch?v=cfDyNPQIJtA&ab_channel=EnterpriseDNA

and the DAX is as below:

LostCustomers =

VAR CurrentCustomers = CALCULATETABLE(VALUES('Transaction Data'[customer_id]),

FILTER(ALL('Transaction Data'[day].[Date]),

'Transaction Data'[day].[Date] > MIN ('Transaction Data'[day].[Date]) - 365 &&

'Transaction Data'[day].[Date] < MIN ('Transaction Data'[day].[Date]) - 90))

VAR PriorCustomers = CALCULATETABLE(VALUES('Transaction Data'[customer_id]),

FILTER(ALL('Transaction Data'[day].[Date]),

'Transaction Data'[day].[Date]> MIN( 'Transaction Data'[day].[Date]) - 90 &&

'Transaction Data'[day].[Date] < MIN('Transaction Data'[day].[Date])))

RETURN COUNTROWS(EXCEPT(CurrentCustomers, PriorCustomers))

 _____________________________________________________________________________________________________________

Now, i want to also add the condition in bold:

Lost Customers - Rolling annual figure - number of transactional consumers who have purchased within last 12 months, but have not done so in over the previous 3 months. After 12 months of no purchases, the user is no longer considered to be lapsed.

How can i achieve this?

Also, i'm not able to see the grand total of the above calculation in the table.

[amitchandak · Accepted solution]

@Anonymous , refer to my two blogs can help

Customer Retention Part 1:
https://community.powerbi.com/t5/Community-Blog/Customer-Retention-Part-1-Month-on-Month-Retention/ba-p/1361529
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

[Anonymous]

Thanks! 
I think this logic is much simpler to understand!

[v-kelly-msft]

Hi  @Anonymous ,

Is your issue solved now?

If so,could you pls mark the reply as answered to close it?

Much appreciated.

Best Regards,
Kelly

Did I answer your question? Mark my post as a solution!