Forum Discussion

qmestu's avatar
qmestu
Icon for Helper IV rankHelper IV
4 years ago
Solved

Min and Max not working

Hi, I have the following table (an example) Year Value Category 2020 5 A 2019 3 A 2018 2 A 2020 8 B 2019 4 B 2020 2 C How can i create a measure that returns the Value for the earliest yea...
Need Help
  • Syk's avatar
    Syk
    4 years ago

    I just re-read your post and missed that you actually wanted the value! Try this

    Value first year = 
var firstyear = CALCULATE(MIN('Table'[Year]),ALLEXCEPT('Table','Table'[Category]))
return CALCULATE(sum('Table'[Value]),'Table'[Year]=firstyear)

     

Syk's avatar
Syk
Icon for Resident Rockstar rankResident Rockstar
4 years ago

try something like this... 

First year = MINX(SUMMARIZE('Table','Table'[Category]),'Table'[Year])

 

  • qmestu's avatar
    qmestu
    Icon for Helper IV rankHelper IV
    4 years ago

    Error message that a single value for year cannot be determined.

    • rohit_singh's avatar
      rohit_singh
      Icon for Solution Sage rankSolution Sage
      4 years ago

      Hi qmestu ,

      Please try this : 

      Earliest value =

      var _category = SELECTEDVALUE(MinYear[Category])

      var _minyear =
      CALCULATE(
      MIN(MinYear[Year]),
      FILTER(
      ALLSELECTED(MinYear),
      MinYear[Category] = _category
      )
      )

      var _minval =
      CALCULATE(
      MIN(MinYear[Value]),
      FILTER(
      ALLSELECTED(MinYear),
      MinYear[Category] = _category && MinYear[Year] = _minyear
      )
      )

      RETURN
      _minval

      Kind regards,

      Rohit


      Please mark this answer as the solution if it resolves your issue.
      Appreciate your kudos! 🙂

    • Syk's avatar
      Syk
      Icon for Resident Rockstar rankResident Rockstar
      4 years ago

      I just re-read your post and missed that you actually wanted the value! Try this

      Value first year = 
var firstyear = CALCULATE(MIN('Table'[Year]),ALLEXCEPT('Table','Table'[Category]))
return CALCULATE(sum('Table'[Value]),'Table'[Year]=firstyear)