Forum Discussion
How to Identify Old Subscription Entries
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
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 RegardsLucien
1 Reply
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 RegardsLucien