Average of years

[icerdeira]

Hi,

I have made a what-if (parameter) to use the years as a parameter. When I put in the segmentation filter 2015 - 2020 it shows me the averages in an amount by years.

I need the average for 2015 and then the average for 2020 separately using my parameter. So far my formula only gives me the mean between 2015 and 2020 but I don't need the years 2016, 2017 and 2019. I use only 2015 and 2020 to get the ROI.

Thanks,

Isaac

[ValtteriN]

Hi,

Here is one way to do this:

AverageYears =

var minYear =MIN('Calendar'[Year])

var maxYear =Max('Calendar'[Year]) return

AVERAGEX(FILTER(AverageYears,or(AverageYears[Year]=maxYear,AverageYears[Year]=minYear)),AverageYears[Value])
I used calendar instead of parameter as a slicer.

End result:

I hope this helps and if it does consider accepting this as a solution and giving the post a thumbs up!

[icerdeira]

Hi, thanks for the answer.

The problem is that I need to save the average separately. The initial average and the final average. Then I use those 2 measures to get de ROI. The year must be taken by a segmentation filter.

Thanks,

Isaac

[ValtteriN · Accepted solution]

In that case you could place the Initial and End averages into variables or even measures and use those in your calculations. E.g. 

AverageYears (only variables)=

var minYear =MIN('YourParameter'[Year])

var maxYear =Max('YourParameter'[Year]) 

var initialAve =  AVERAGEX(FILTER(AverageYears,AverageYears[Year]=minYear),AverageYears[Value])
var endAve =  AVERAGEX(FILTER(AverageYears,AverageYears[Year]=maxYear),AverageYears[Value])
return
//Now use these to get the values you want