Comparing Periods from one list

[JamesM_Hop]

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?

[v-chuncz-msft]

@JamesM_Hop,

It seems to be right, please illustrate with an example.