Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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
Solved! Go to 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
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
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
111 | |
97 | |
80 | |
69 | |
59 |
User | Count |
---|---|
150 | |
119 | |
104 | |
87 | |
67 |