topic Average of years in DAX Commands and Tips

Average of years

icerdeira — Wed, 22 Dec 2021 10:17:14 GMT

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

Re: Average of years

ValtteriN — Wed, 22 Dec 2021 10:49:43 GMT

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!

Re: Average of years

icerdeira — Wed, 22 Dec 2021 11:06:04 GMT

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

Re: Average of years

ValtteriN — Wed, 22 Dec 2021 11:14:07 GMT

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