How to Identify Old Subscription Entries

[cjones97]

I have a dataset with multiple subscription entries (with start and end dates). For a particular name, I want to create a column that identifies the old subscription (but only if it renewed), so I can filter it out. Thank you for any and all help!

For a particular Name, if this row's End Date - another row's Start Date = - 1, then 1, if not 0

Entry 1: Name = George; Start Date = 1/1/2021; End Date = 12/31/2021, New Column = 1

Entry 2: Name = George; Start Date = 1/1/2022; End Date = 12/31/2022, New Column = 0

[v-luwang-msft · Accepted solution]

HI @cjones97 ,

Pls test the below dax to create the below two column:

rank = RANKX(FILTER('Table','Table'[Name ]=EARLIER('Table'[Name ])),'Table'[Start Date],,DESC,Dense) 

Column = IF(DATEDIFF( 'Table'[End Date], CALCULATE(MAX('Table'[Start Date]),FILTER(ALL('Table'),'Table'[rank]=EARLIER('Table'[rank])-1&&'Table'[Name ]=EARLIER('Table'[Name ]))),DAY)=1,1,0)

Output result:

Did I answer your question? Mark my post as a solution!

Best Regards

Lucien