Calculating lapsed consumers - table with no transaction info

[ING_BT]

Hello everyone! 

I am well aware that some posts already exist on this topic, however, I am having issues adapating the solutions to my situation: 

The definition of a lapsed consumer: # of 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.

I have a DimDate table. 

The other table is the customer table. Where I have a specific customer ID. This table is populated each time a customers is making a purchase (same customer will get the same customer ID). There is a Date column (connected to my date table). 

This table do not have any other transactional information (order, value, etc.)

I believe I need to translate this into DAX (or calculated columns?):

"If a customer ID is present within the last 12 months, but is no present within the last 3 months, then count as "Lapsed" (or add Lapsed in a calculated column? and then add a count measure?) But, if the customer ID is not present in the last 12 months show "Not Lapsed" (or do not count as Lapsed)". 

Fields names: 
Date: DimDate[Date]

Customer ID: Customer_table[customerID]

The main goal is to: COUNT the number of Lapsed customers over the selected period (typical date filters) 

Thank you for your help and guidance!

Bt. 

[v-yiruan-msft]

Hi @ING_BT ,

I created a sample pbix file(see attachment), please check whether that is what you want.

In addition, you can refer the content in the following blogs to achieve it:

Lost Customers DAX Calculation for Power BI

Attrition Analysis: Finding Lost Customers Using Power BI & DAX

Best Regards

[ING_BT]

Hello @v-yiruan-msft !

Thank you very much for you detailed awnser. 

I having troubles to reproduice this measure. I have the exact same parameters that in you files, its weird. 

I do have another question, how would you adapt this measure to instead having to select only one date, to be able to select a date range? 

Thank you, 

Bt.

[v-yiruan-msft]

Hi @ING_BT ，

What is the calculation logic when you select a date range? If you select the dates from 2020.1.12 to 2020.8.20, do you want to get the number of lapsed consumers in this time period? Does lapsed customer refer to users who purchased between 2019.1.11 and 2020.1.11 but did not purchase between 2020.1.12 and 2020.8.20? Could you please provide more details of the related logic with sample or screenshot? Thank you.

Best Regards

[ING_BT]

Hi @v-yiruan-msft 

The logic of the date range would be just to use the first selected date as a marker! So in your example from 2020.1.12 to 2020.8.20 we would consider 2020.1.12 as being the date from which our calculation would be based on (so the selected value) 

If that makes sens ? 🙂 

[v-yiruan-msft · Accepted solution]

Hi @ING_BT ,

I updated my sample pbix file (the part with red line/square is updated one), please check whether that is what you expected.

Best Regards

[ING_BT]

Do you know why it doesn't work anymore when a relationship is created between the date table and the date column of the customer table ?

Thank you!

[ING_BT]

Hi @v-yiruan-msft ! 

Thank you very much for your help 😁

Have a good day,

Bt