Minimum value of the month inside the year

[Aspiring_Developer]

Hello Everyone,

I need to calculate below:-

In chart i have Year as Dimension and Measure is Avg(Average Hourly Rate). We have data for 3 years.

Now i want to calculate Best of 2019 like below

In year 2019 , i have values for 12 months. So if the year is maximum , it should give me the minimum value for the months present in the latest year.

Can any one please help:-

For below , the maximum year is 2019, so it should return the ,minimum value from the months present in 2019.

@sunny_talwar  @Kushal_Chawda 

[Kushal_Chawda · Accepted solution]

@Aspiring_Developer  try below. Create table with Dimension Year and below two expressions

// for value

=FirstSortedValue(aggr(sum({<Year={"$(=max(Year))"}>}Value),Year,MonthYear),aggr(sum({<Year={"$(=max(Year))"}>}Value),Year,MonthYear))

// for month

=FirstSortedValue(MonthYear,aggr(sum({<Year={"$(=max(Year))"}>}Value),Year,MonthYear))

[sunny_talwar]

For Value, you can just do this

Min(Aggr(Sum({<Year = {"$(=Max(Year))"}>} Value), Year, MonthYear))

[Aspiring_Developer]

@sunny_talwar 

It should return the minimum value ie.  like below Year 2020 , Feb2020 has the minimum value i.e 68843.196

[sunny_talwar]

What is this returning?

Min(Aggr(Sum({<Year = {"$(=Max(Year))"}>} Value), Year, MonthYear))