Calculating Tenure when your Cancel Date is in the future

A configuration on our EComm system requires that every subscriber have a NOT NULL Cancel Date.  This presented some difficulty for calculating tenure, particularly since the default for paying subscribers is 2999-01-01.  Wow!  That's some great tenure.  They've been paying subscribers longer than we've been in business.

For the past few weeks I've been grappling with how to adjust the CancelDt and calculate tenure.  Since I'm using Tableau on top of Teradata, I've dug into both and decided on the below.

These calculations are included in a monthly churn workbook I produce for our product team.  The workbook is updated at the beginning of each month and analyzes churn and media adoption for the previous month, it's preceding three month average, and the same periods in the previous year.  The dates are configured to update dynamically, so the current_date assignment is valid for the entire month the workbook is valid.  Once the workbook updates for the following month, you'll be ahead and the current_date assignment will remain in the month ahead of the study month.

Previous method for adjusting the CancelDt and calculating churn:

In Teradata create a view or use custom query for Tableau

select RptMonth
, Id
, SignupDt - (EXTRACT(DAY from SignupDt) -1) as SignupMo
, CancelDt - (EXTRACT(DAY from CancelDt) -1) as CancelMo
from foo

Then in Tableau, create a custom metrics to resolve the future date
"CancelMo Truncated"
IIF([CancelMo] > TODAY(), DATETRUNC('month',TODAY()),[CancelMo])

and  to calculate "Tenure"

DATEDIFF('month',[SignupMo],[CancelMo Truncated])


Updated Method
Push it all back to the database:


select RptMonth
, Id
, SignupDt 
, CASE WHEN CancelDt = '2999-01-01' THEN current_date ELSE CancelDt END CancelDtUpd
, (CancelDtUpd - SignupDt) month(4) as Tenure
, CASE WHEN CancelDt = '2999-01-01' THEN 1 ELSE 0 END PayingFlag
from foo