Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
7 years ago

Max of Filtered Range Not working

This seems like it should be very simple, but all my attempts have failed at this point. I'm trying to use the below table to create a new column in a "output" table, that lists the "Most Recent Fiscal Year" from a "raw data" table, for each "institution". 

 

Raw Data Table

Fiscal Year    Insitution
2008              Abilene Christian University
2009              Abilene Christian University
2010              Abilene Christian University
2011              Abilene Christian University
2012              Abilene Christian University
2013              Abilene Christian University
2014              Abilene Christian University
2015              Abilene Christian University
2016              Abilene Christian University
2017              Abilene Christian University
2018              Abilene Christian University
2007              Alcorn State University - E&G
2008              Alcorn State University - E&G
2009              Alcorn State University - E&G
2010              Alcorn State University - E&G
2011              Alcorn State University - E&G
2012              Alcorn State University - E&G
2013              Alcorn State University - E&G
2014              Alcorn State University - E&G
2015              Alcorn State University - E&G
2016              Alcorn State University - E&G

 

Output table

Insitution                                   Goal Output
Abilene Christian University       2018
Alcorn State University - E&G    2016

 

This is the formula I am using that seemed to be working for awhile, but has started to just populate "2018" for all insitutions, even though it's not the max. I'm assuming that my filter is somehow not working, but I'm not sure...

 

Most Recent Fiscal Year =
   CALCULATE(
      MAX(SpaceProfile[Fiscal Year.Fiscal Year]),
      FILTER('Institution Lookup','3yr Averages'[School Systems.Campusname] = EARLIER('3yr Averages'[School Systems.Campusname]))
)
Need Help

11 Replies

  • parry2k's avatar
    parry2k
    Icon for Super User rankSuper User
    7 years ago

    Anonymous can you try

     

    Most Recent Fiscal Year = 
   CALCULATE(
      MAX(Table[Fiscal Year]),
ALLEXCEPT( Table, Table[Institution] )
)
    • Anonymous's avatar
      Anonymous
      Not applicable
      7 years ago

      It's still just populating 2018 for all institutions.

      • parry2k's avatar
        parry2k
        Icon for Super User rankSuper User
        7 years ago

        Anonymous are you adding column or measure?