Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
2 years ago
Solved

Identify max value in a column

I would like to create a custom column that returns the text " Latest Year" in any row where the year contains the max value. 

 

 

StatusYear
Budget2024
Budget2023
Budget2022
Budget2021

 

The end result would be below

 

 

StatusYearNew Column
Budget2024Latest Year
Budget2023 
Budget2022 
Budget2021 

 

Need Help
  • Fowmy's avatar
    Fowmy
    2 years ago

    Anonymous 
    Yes you can:

    IF (
    Tablename[Year] = 
    CALCULATE(
        MAX( Tablename[Year] ),
         ALLEXCEPT( Tablename, Tablename[Status] )
    ), 
    "Latest Year"
)

6 Replies

  • Fowmy's avatar
    Fowmy
    Super User
    2 years ago

    Anonymous 
    Try this calculated column:

    New Collumn = IF ( tablename[year] = MAX( tablename[year] ) , "Latest Year")

     

    • Anonymous's avatar
      Anonymous
      Not applicable
      2 years ago

      Fowmy thank you this is a good start for me. I actually have two categores that i need to calculate the latest year for within the same column. One is budget and the other is forecast. Is there a way to place the " Latest Year" identifier  for each category. see the example below.

       

       

      StatusYearNew Column
      Budget2024Latest Year
      Budget2023 
      Budget2022 
      Budget2021 
      Forecast2311Latest Year
      Forecast2308 
      Forecast2306 

       

      I was originally going to use a custom column in query editor  to identify the latest year for each category (Budget and forecast) and then I would have appended the columns together.

       

      • Fowmy's avatar
        Fowmy
        Super User
        2 years ago

        Anonymous 
        Yes you can:

        IF (
    Tablename[Year] = 
    CALCULATE(
        MAX( Tablename[Year] ),
         ALLEXCEPT( Tablename, Tablename[Status] )
    ), 
    "Latest Year"
)
  • Dangar332's avatar
    Dangar332
    Resident Rockstar
    2 years ago

    HI, Anonymous 
    Anonymous 
    try below code for new column 

    just adjust your table name

     

     

     

    Column = 
var a= 'Table (2)'[status]
var b =
MAXX(FILTER('Table (2)','Table (2)'[status]=a),'Table (2)'[year])
return
IF('Table (2)'[year]=b,"latest year",BLANK())Table (2)'[status]=a),'Table (2)'[year])