Forum Discussion

cmcgo3's avatar
cmcgo3
Helper II
2 years ago
Solved

Filtering Visual for Consecutive Years

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 n...
Need Help
  • ryan_mayu's avatar
    ryan_mayu
    2 years ago

    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
ryan_mayu's avatar
ryan_mayu
Super User
2 years ago

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's avatar
cmcgo3
Helper II
2 years ago

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's avatar
    ryan_mayu
    Super User
    2 years ago

    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's avatar
      cmcgo3
      Helper II
      2 years ago

      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's avatar
        ryan_mayu
        Super User
        2 years ago

        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