Forum Discussion
Customer Lifetime Calculations
Hey everyone,
Working on some basic customer lifetime calculations.
The goal is to measure, in months, how long each customer has been with the company.
Each customer is labeled with a unique ID and Created At Date in Table A. However, each customer can have multiple subscriptions. Of those subscriptions some may be cancelled and some may be active. We define a cancelled customer as fully cancelled ONLY when ALL their subscriptions are cancelled. Each subscription is tied to the customer ID and an appropriate cancellation date in Table B. See Below:
Table A (Customers)
Cust ID, Cust Name, Customer Created at Date
001, John Smith, 01/01/2017
002, Sally May, 02/10/2017
Table B (Subscriptions)
Subscription ID, Customer ID, Cust Name, State, Cancelled at Date
111, 001, John Smith, Active, blank
112, 001, John Smith, Cancelled, 02/02/2018
113, 002, Sally May, Cancelled, 03/10/2017
I need to be able to find the average customer lifetime in months for all customers. How do I base this calculation on the Created At Date and a date that appears in a chart for customers who are not fully cancelled BUT use the created at date and the most recent cancellation date for customers if they are fully cancelled?
I can already find the lifetime in months for all customers based on the created at date in Table A and the date in a chart or filter. I just need to be able to tailor this to capture the time between fully cancelled date and created at date for cancelled customers.
Table A & B are dimension tables, connected through my Fact Table. I also have a floating date table not connected to these two tables.
Let me know!
Thanks ahead of time
Hi bryceBI ,
I renewed the demo. Please download it from the attachment.
I deleted all the relationship and created a measure.
Measure = VAR currentStatus = CALCULATE ( COUNTROWS ( 'Subscriptions' ), FILTER ( ALL ( Subscriptions ), Subscriptions[Customer ID] = MIN ( Customers[Cust ID] ) && Subscriptions[State] = "Active" ) ) VAR dateToUse = IF ( currentStatus >= 1, MAX ( 'Calendar'[Date] ), CALCULATE ( MAX ( Subscriptions[Cancelled at Date] ), FILTER ( ALL ( Subscriptions ), Subscriptions[Customer ID] = MIN ( Customers[Cust ID] ) ) ) ) RETURN DATEDIFF ( MIN ( Customers[Customer Created at Date] ), dateToUse, MONTH )
Best Regards,
3 Replies
Hi bryceBI ,
The [Table B] is more like a fact table. I would suggest you create a relationship based on [Cust ID] and then add a calculated column. Please download the demo from the attachment.
Status = VAR activeLines = COUNTROWS ( FILTER ( RELATEDTABLE ( Table2 ), 'Table2'[State] = "Active" ) ) VAR maxCancelDate = MAXX ( RELATEDTABLE ( Table2 ), [Cancelled at Date] ) RETURN IF ( activeLines > 0, DATEDIFF ( [Customer Created at Date], TODAY (), MONTH ), DATEDIFF ( [Customer Created at Date], maxCancelDate, MONTH ) )
Best Regards,
Hey thanks for your response. I follow your logic on the below calculated column you provided and it is taking me in the right direction.
This lifetime calculation needs to be viewed for any given time period based on date in a chart axis or on a filter. For this reason I feel like using Today (), for the lifetime calculation would not yield the corect result when placed in a chart with Date in the axis field. Thoughts?
Also - could I use this same logic but maintain the current relationships I have in my file? The data we discussed above are in two DIM tables and a relationship being added between the two may break other parts of my file.
Excited to hear back from you, thank you again for your attention!
Hi bryceBI ,
I renewed the demo. Please download it from the attachment.
I deleted all the relationship and created a measure.
Measure = VAR currentStatus = CALCULATE ( COUNTROWS ( 'Subscriptions' ), FILTER ( ALL ( Subscriptions ), Subscriptions[Customer ID] = MIN ( Customers[Cust ID] ) && Subscriptions[State] = "Active" ) ) VAR dateToUse = IF ( currentStatus >= 1, MAX ( 'Calendar'[Date] ), CALCULATE ( MAX ( Subscriptions[Cancelled at Date] ), FILTER ( ALL ( Subscriptions ), Subscriptions[Customer ID] = MIN ( Customers[Cust ID] ) ) ) ) RETURN DATEDIFF ( MIN ( Customers[Customer Created at Date] ), dateToUse, MONTH )
Best Regards,
