Filtering Visual for Consecutive Years

[cmcgo3]

I was given a solution back in February, but looking for additional options to resolve.

This is probably an easy fix, but I cannot figure out if I need a calculated column or DAX measure.  I do not get the result I want when applying a filter in visual (clustered bar chart).

I have Player 'A' and Player 'B' that participated in both Year 2022 and 2023.  Players 'C' only participated in 2023, while Player 'D' only particpated in 2022.  

I need a filter that excludes Players 'C' and 'D' because I only want to show players that particpated in consecutive years 2022 and 2023.

  filter.pbix 

[ryan_mayu]

@cmcgo3 

pls try to create a column

Column =

var _last=maxx(FILTER('Table','Table'[player]=EARLIER('Table'[player])&&'Table'[year]=EARLIER('Table'[year])-1),'Table'[year])

VAR _next=maxx(FILTER('Table','Table'[player]=EARLIER('Table'[player])&&'Table'[year]=EARLIER('Table'[year])+1),'Table'[year])

return if (not(ISBLANK(_last))||not(ISBLANK(_next)),"y")

pls see the attachemnt below

[cmcgo3]

Thank you Ryan, it works just like it is written.  I need one small edit though, If i want the max year to = 2024 which should return all blanks, how would I adjust this calculation?? Thanks!!

[ryan_mayu]

what do you mean by max year to 2024? if has Consecutive Years until 2024, then all be blank?

could you pls update the sample data and expected output?

[cmcgo3]

Hello Ryan,  I went in and added Year 2024 for Player A, Player C, and Player D.  Now in visual, Player A, B, and C are showing.  Because the way the column is written, Player B shows up because of the year 2023 and 2022.  Since the current year is 2024, I only want Player A and Player C to show up on chart.  I was wondering what next step would be to adjust your column you created?  I hope I explained well...

https://1drv.ms/u/s!AuqlAZe1G3j7lUmnTs0wRdND6YoQ?e=MODoUS 

[ryan_mayu · Accepted solution]

@cmcgo3 

pls try this

Column =

var _last=maxx(FILTER('Table','Table'[player]=EARLIER('Table'[player])&&'Table'[year]=EARLIER('Table'[year])-1),'Table'[year])

VAR _next=maxx(FILTER('Table','Table'[player]=EARLIER('Table'[player])&&'Table'[year]=EARLIER('Table'[year])+1),'Table'[year])

VAR _currentyear=maxx(FILTER('Table','Table'[player]=EARLIER('Table'[player])&&'Table'[year]=year(today())),'Table'[year])

return if(ISBLANK(_currentyear),BLANK(),if(not(ISBLANK(_last))||not(ISBLANK(_next)),"y"))

pls see the attachment below