Forum Discussion
Customer / Member Tenure
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:
Thank you for your kind assistance.
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.
Best Regards,
Liang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
2 Replies
- 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) 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.
Best Regards,
Liang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
