Showing minimum data among last 3 years

[Aspiring_Developer]

Hello,

Please help on this one :-

Data

Year, Data, AVG(Data) 

2017 50   1

2018 20.   2

2019 30.  3

2020 5.  4

My requirement is to calculate the min of AVG(Data)

So that I should get min(AVG (Data) = 1 as 1 is the minimum value in the above table 

And we have to apply this only for latest 3 years . As of now I have the data for till 2020

I have to show it in bar graph with dimension as Year . How can I achieve this ? 

@sunny_talwar  @Kushal_Chawda 

[sunny_talwar]

For last 3 years, may be add @Kushal_Chawda's set analysis to his if expression

If(Avg({<Year = {">=$(=Max(Year)-3)"}>} Data) = Min({<Year = {">=$(=Max(Year)-3)"}>} TOTAL Aggr(Avg({<Year={">=$(=Max(Year)-3)"}>} Data), Year)), Avg({<Year={">=$(=Max(Year)-3)"}>} Data), 0)

[Aspiring_Developer]

@sunny_talwar 

Tried the above, giving the data as 0 in entire column :'(

[Aspiring_Developer]

@sunny_talwar 

[sunny_talwar]

You didn't mention about Project Code dimension... may be try this

If(Avg({<Year = {">=$(=Max(Year)-3)"}>} Data) = Min({<Year = {">=$(=Max(Year)-3)"}>} TOTAL <[Project Code]> Aggr(Avg({<Year={">=$(=Max(Year)-3)"}>} Data), Year)), Avg({<Year={">=$(=Max(Year)-3)"}>} Data), 0)

The response is dependent upon the information we have.

[Aspiring_Developer]

@sunny_talwar 

sorry,

it is coming zero without project_code :

[Kushal_Chawda · Accepted solution]

I think you confused us with min(Avg(Data)). Looks like you already have average calculated so you need minimum of that for particular year. 

Try below with dimension Year and ProjectCode and below expression

=if(sum([avg hourly Rate])=sum(total <Year>aggr(min([avg hourly Rate]),Year)),sum({<Year ={">=$(=max(Year)-3)"}>}[avg hourly Rate]),0)

[Kushal_Chawda]

Make sure to suppress zero values