Forum Discussion

ING_BT's avatar
ING_BT
Icon for Helper I rankHelper I
5 years ago
Solved

Calculating lapsed consumers - table with no transaction info

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. 

7 Replies

    • ING_BT's avatar
      ING_BT
      Icon for Helper I rankHelper I

      Hello Anonymous !

      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.

      • Anonymous's avatar
        Anonymous
        Not applicable

        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