Forum Discussion

qmestu

Helper 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 year, for each category.

I tried the following:

Value (earliest) =
var firstYear=minx('table','table'[Year])
return CALCULATE(sum(Value),'table'[Category]="A",'table'[Year]=firstYear)

But it doesn't work. It seems that min, minx or maxx always return the highest value.

Thanks for any help.

Need Help

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)

6 Replies

Syk
Resident Rockstar
4 years ago
try something like this...
First year = MINX(SUMMARIZE('Table','Table'[Category]),'Table'[Year])
- qmestu
  Helper IV
  4 years ago
  Error message that a single value for year cannot be determined.
  - rohit_singh
    Solution 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! 🙂
ribisht17
Super User
4 years ago
Step 1
Latest Year=calculate(max(year),ALLEXEPT(TABLE,CATEGORY))

Step2
Latest Value=IF(max(Year)=Latest Year,sum(value),0)

Filter out 0

Regards,
Ritesh
- qmestu
  Helper IV
  4 years ago
  The first step returns the same value wether i use min or max.