Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
cjones97
Frequent Visitor

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

1 ACCEPTED SOLUTION
v-luwang-msft
Community Support
Community Support

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:

vluwangmsft_0-1656922859874.png

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


Best Regards

Lucien

View solution in original post

1 REPLY 1
v-luwang-msft
Community Support
Community Support

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:

vluwangmsft_0-1656922859874.png

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


Best Regards

Lucien

Helpful resources

Announcements
July 2024 Power BI Update

Power BI Monthly Update - July 2024

Check out the July 2024 Power BI update to learn about new features.

PBI_Carousel_NL_June

Fabric Community Update - June 2024

Get the latest Fabric updates from Build 2024, key Skills Challenge voucher deadlines, top blogs, forum posts, and product ideas.

Top Solution Authors