How to find max value and Minvalue

[kunkumnaveen]

Hello All,

I need to find Max Year value  and Min year value  for Each  combination of  Product,Model,Mission,Type...

For example in below sample data set 

Product	Model	Mission	Type	Year	Values
DRA	4WD AT	AT	Sel	2011	1923
DRA	4WD AT	AT	Sel	2012	1923
DRA	4WD AT	AT	Sel	2013	1923
DRA	4WD AT	AT	Sel	2014	1932
DRA	4WD AT	AT	Sel	2015	1950
DRA	4WD AT	AT	Sel	2016	1950
DRA	4WD AT	AT	Sel	2017	1950
UTI	FASS	MT	line	2012	705
UTI	FASS	MT	line	2013	705
UTI	FASS	MT	line	2014	725
UTI	FASS	MT	line	2015	725
UTI	FASS	MT	line	2016	725
UTI	FASS	MT	line	2017	725
UTI	FASS	MT	line	2018	725
UTI	FASS	MT	line	2019	727

Measure i wrote  to get max and Min

1 )Max :    Only({<Year={$(=max(Year)}>}Values)

2)Min :  Only({<Year={$(=min(Year)}>}Values)

I am getting error 

Output:

Max value for 1st row is 1950 (Max year =2017)

Max value for 2nd row is 727 (Max year =2019)

PFA..

Thanks

Naveen

[sunny_talwar]

Try this instead

For Max

FirstSortedValue(Values, -Year)

For Min

FirstSortedValue(Values, Year)

[kunkumnaveen]

Thanks Sunny ,
What i am trying to achieve is a CAGR value and the formula is below ...
Pow(
(
only({<Year={$(=max(Year))}>}Values)

/
only({<Year={$(=Min(Year))}>}Values)
),
1/(Max(Year)-Min(Year))-1
)

so how to fit firstsortvalue in the place of max Year and Min Year

[sunny_talwar · Accepted solution]

May be this

Pow(
(FirstSortedValue(Values, -Year)/FirstSortedValue(Values, Year)),
1/(Max(Year)-Min(Year))-1
)

[sunny_talwar]

Check this out

[kunkumnaveen]

Thanks Sunny , when ever there is a show stopper , u always bring me out of that , i am keep learning from you ...
Once again Thanks Man

[kunkumnaveen]

Need a suggestion how to add default selection value in firstsortvalue 

I mean My default  it should take 

Feature = CC

as Shown below 

Before Feature column Selection 

After feature Column selection 

I tried this

Pow(
(FirstSortedValue({<Feature={'CC'}>}Values, -Year)/FirstSortedValue({<Feature={'CC'}>}Values, Year)),
1/(Max({<Feature={'CC'}>}Year)-Min({<Feature={'CC'}>}Year))-1
)

[kunkumnaveen]

Need a suggestion how to add default selection value in firstsortvalue 

I mean My default  it should take 

Feature = CC

as Shown below 

Before Feature column Selection 

After feature Column selection 

I tried this

Pow(
(FirstSortedValue({<Feature={'CC'}>}Values, -Year)/FirstSortedValue({<Feature={'CC'}>}Values, Year)),
1/(Max({<Feature={'CC'}>}Year)-Min({<Feature={'CC'}>}Year))-1
)

[sunny_talwar]

May be this

If(GetSelectedCount(Feature) = 0,

Pow(
(FirstSortedValue({<Feature={'CC'}>}Values, -Year)/FirstSortedValue({<Feature={'CC'}>}Values, Year)),
1/(Max({<Feature={'CC'}>}Year)-Min({<Feature={'CC'}>}Year))-1
),

Pow(
(FirstSortedValue(Values, -Year)/FirstSortedValue(Values, Year)),
1/(Max(Year)-Min(Year))-1
)
)