Min and Max not working

[qmestu]

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.

[ribisht17]

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]

The first step returns the same value wether i use min or max.

[Syk]

try something like this... 

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

[qmestu]

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

[Syk · Accepted solution]

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)

[rohit_singh]

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! 🙂