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 

[Kushal_Chawda]

try below expression

=min({<Year={">=$(=max(Year)-3)"}>}Avg_Data)

[Aspiring_Developer]

@Kushal_Chawda  hello kush ,firstly many thanks for your instant response and I am struggling due to tight deadline. 

As per your solution,

In my data I am getting only 2 feilds from backend Year & Data

AVG (Data) I have valuated in front measure 

I am not sure if I can mass this front end measure in the expression. Please confirm

[Aspiring_Developer]

---

@Aspiring_Developer wrote:

@Kushal_Chawda  hello kush ,firstly many thanks for your instant response and I am struggling due to tight deadline. 

As per your solution,

In my data I am getting only 2 feilds from backend Year & Data

AVG (Data) I have valuated in front measure 

I am not sure if I can mass this front end measure in the expression. Please confirm

---

@Kushal_Chawda 

Desired output

Year Data  

2017. 50

2018 20

2019 30

2020 5

Desired output 

To get measure column

Min(AVG(Data)) 

Can we use the calculated measure column AVG(Data) to get the other column measure Main(AVG ( Data)) returning minimum values

[Kushal_Chawda]

What do you mean by this (Min(Avg(Data))?

[Kushal_Chawda]

may be you are looking for this

=sum({<Year={">=$(=max(Year)-3)"}>}Data)

[Aspiring_Developer]

@Kushal_Chawda

Avg ( Data ) will give us the average values for each year. I want to show the minimum values for these AVG(Data) in a chart like below

Year AVG( data)  minimum of AVG(Data)

2017 20.                 20

2018 30                   -

So the column should 20 as it is the small average values in the years 

[Kushal_Chawda]

try below with Year as dimension and below expression

if(avg(Data)= min(total aggr(avg(Data),Year)),Avg(Data),0)

Go to chart properties->add-on-> uncheck include zero values 

[Aspiring_Developer]

@ Kush 

if(avg(Data)= min(total aggr(avg(Data),Year)),Avg(Data),0)

I want to show it for last 3 years . If i will take Year, it will give  me the data for all Years.

@sunny_talwar 

[Aspiring_Developer]

@Kushal_Chawda 

Used the expression, didn't worked

See attached screenshot please