Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Calling all Data Engineers! Fabric Data Engineer (Exam DP-700) live sessions are back! Starting October 16th. Sign up.

Reply
ClaytonL
Frequent Visitor

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,

 

 

Test Data.JPG

3 REPLIES 3
v-xiaotang
Community Support
Community Support

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
Frequent Visitor

@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.

 

error in the data.JPG

amitchandak
Super User
Super User

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

Share with Power BI Enthusiasts: Full Power BI Video (20 Hours) YouTube
Microsoft Fabric Series 60+ Videos YouTube
Microsoft Fabric Hindi End to End YouTube

Helpful resources

Announcements
FabCon Global Hackathon Carousel

FabCon Global Hackathon

Join the Fabric FabCon Global Hackathon—running virtually through Nov 3. Open to all skill levels. $10,000 in prizes!

October Power BI Update Carousel

Power BI Monthly Update - October 2025

Check out the October 2025 Power BI update to learn about new features.

FabCon Atlanta 2026 carousel

FabCon Atlanta 2026

Join us at FabCon Atlanta, March 16-20, for the ultimate Fabric, Power BI, AI and SQL community-led event. Save $200 with code FABCOMM.