Forum Discussion

cjones97's avatar
cjones97
Frequent Visitor
4 years ago
Solved

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 ca...
Need Help
  • v-luwang-msft's avatar
    v-luwang-msft
    4 years ago

    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

v-luwang-msft's avatar
v-luwang-msft
Community Support
4 years ago

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