Forum Discussion

ClaytonL's avatar
ClaytonL
Frequent Visitor
4 years ago

Recurring Memberships based on Unique User IDs

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,

 

 

3 Replies

  • amitchandak's avatar
    amitchandak
    Icon for Super User rankSuper User
    4 years ago

    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")

  • ClaytonL's avatar
    ClaytonL
    Frequent Visitor
    4 years ago

    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.

     

  • v-xiaotang's avatar
    v-xiaotang
    Icon for Community Support rankCommunity Support
    4 years ago

    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.