Customer / Member Tenure

[Anonymous]

Hello Community.

I am seeking your assistance to create a measure for customer (member) tenure. I would like the mesaure to calculate tenure based on the period of time between 'membership_start_date' and the latest 'order_date'.

My example dataset is offered below:

member_name	membership_start_date	order_date	tenure_years_at_last_order
name 1	10/12/2018	15/11/2019	0.93
name 2	19/05/2014	18/01/2020	5.67
name 3	12/03/2019	03/12/2019	0.73
name 1	13/02/2019	13/02/2020	1.00
name 3	05/08/2018	02/01/2020	1.41
name 1	30/10/2016	15/03/2020	3.37
name 2	22/02/2018	19/02/2020	1.99

To assist, I have a standard DateKey available to use.

I have created a general tenure column that calculates tenure based on 'membership_start_date' up to and including today's date, but I am also interested to evaluate tenure upto and including the 'Last Order' date. This will be useful particularly to evaluate the tenure of members who have cancelled their membership, enabling my team to contact valuable members with a long tenure in particular.

I am seeking your assistance as I have not been able to successfully adapt my general tenure code to suit this new purpose: 

Member Tenure_nmbr (Today) = FLOOR(IF('UNION Transaction Table'[membership_start] < TODAY(), DATEDIFF('UNION Transaction Table'[membership_start], TODAY(), DAY) / 365.25, 0), 1)

Thank you for your kind assistance.

[V-lianl-msft · Accepted solution]

Hi @Anonymous ,

Is this the result you expected?

Or do you want to calculate by the last order date of each customer?

I created a pbix to see if it meets your needs.

Sample .pbix

Best Regards,
Liang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Greg_Deckler]

Perhaps:

Member Tenure_nmbr (Today) = FLOOR(IF('UNION Transaction Table'[membership_start] < TODAY(), DATEDIFF('UNION Transaction Table'[membership_start], MAXX(FILTER('Table',[member_name] = EARLIER([member_name])),[order_date]), DAY) / 365.25, 0), 1)