Join us at FabCon Atlanta from March 16 - 20, 2026, for the ultimate Fabric, Power BI, AI and SQL community-led event. Save $200 with code FABCOMM.
Register now!Calling all Data Engineers! Fabric Data Engineer (Exam DP-700) live sessions are back! Starting October 16th. Sign up.
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,
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.
@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.
@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")
Join the Fabric FabCon Global Hackathon—running virtually through Nov 3. Open to all skill levels. $10,000 in prizes!
Check out the October 2025 Power BI update to learn about new features.
User | Count |
---|---|
10 | |
6 | |
3 | |
3 | |
3 |
User | Count |
---|---|
13 | |
11 | |
9 | |
8 | |
8 |