Recurring Memberships based on Unique User IDs

[ClaytonL]

Hello Everyone,

I am working through a problem that I cannot find an answer to across the forums yet. I am trying to come up with a way to track membership data. The problem is the way I get the data does not come with a specific column called membership. There are a multitude of membership types across a number of locations that are all classified as "Service". However, there are tons of other things classified as "Service as well. For context, I do not want to add a calculated column to identify memberships as my data updates daily and has over 1 million rows. 

What I am trying to figure out is membership retention. I would like to be able to track how long a customer who purchases a membership keeps renewing. I would like the first instance of a membership purchase to be a new member, every recurring month they would be classified as a recurring member, until they cancel and will be a terminated member.

The data below is a scrubbed version of what I have. The orderIDs are unique however, the UserID is not. I was thinking that a dax measure that looked back one month to see if that userID bought a membership would work but I cannot figure out how to do it. Also, for terminated members, a dax measure could look back one month, see if they bought last month but didn't this month. 

I am stumped on this one and hope someone can help. I have the scrubbed data in powerbi but am unable to upload it as I am a new member.

Thanks,

[v-xiaotang]

Hi @ClaytonL 

Thanks for reaching out to us.

>> The data below is a scrubbed version of what I have. ... I was thinking that a dax measure that looked back one month to see if that userID bought a membership would work but I cannot figure out how to do it. Also, for terminated members, a dax measure could look back one month, see if they bought last month but didn't this month. 

Could you provide your expected outcome based on your sample data? Note: Your sample data should preferably contain all the scenarios involved. For example,

if Service= "Membersship", and if Date .... then measure= ....

if Service= "Basic Membersship", and if Date .... then measure= ....

...

Thanks.

Best Regards,

Community Support Team _Tang

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

[ClaytonL]

@amitchandak,
Any idea why I would get an error on the earlier function? It is saying it cant find the [UserID] column. I've even tried inputting BillingReportData.[UserID] (the home for the column) but it still doesnt work.

[amitchandak]

@ClaytonL , You can have a column for that

new column =

var _min =minx(filter(Table, [UserID] = earlier([USerID]), [Date])

return

if(datediff(_min, [Date], month) >0, "Retained", "New")