Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
Hey all,
So I have a data set which is user IDs alongside the dates they have made purchases (though these dates are simplified to be the start of the month as I only need to know which months they have purchased in). This have been de-duped so that a users ID can only appear once in each month.
Using this 2 column table, I would like to generate a report that shows a rolling retention. So for each month, compare the User IDs to the previous 12 months and if they appear in that 12 months, they are considered retained. So I will end up with an extra "retained" column which shows the total number against each month like the attached image.
I have tried the following formula but the numbers are not coming out correct:
Retatined = IF( COUNTROWS( FILTER('UK Bookings 2015-2017','UK Bookings 2015-2017'[EMAIL]=EARLIER('UK Bookings 2015-2017'[EMAIL]) && 'UK Bookings 2015-2017'[Month]<EARLIER('UK Bookings 2015-2017'[Month]) && 'UK Bookings 2015-2017'[Month]>=EARLIER('UK Bookings 2015-2017'[MonthLastYear])))>0 ,1)
Any ideas?
It seems to be right, please illustrate with an example.
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
118 | |
107 | |
69 | |
68 | |
43 |
User | Count |
---|---|
148 | |
104 | |
102 | |
89 | |
66 |