Hi, I’m blocked trying to calculate the annual average of the column [Rain (mm)] for all the years of the table.

So first I need to SUM the Rain of all the rows of each year and make an average of that.

I want to generate a view that shows 1 row with the value of the annual average.

Then I intend to do the same with the month average. But that is different becouse I need to SUM the Rain of all the rows of the actual month for each year and make an average of that.

I want to include in the previous view the historic average of the actual month.

An example would be like this:

Data:

2019 —> 850 mm (the SUM of all the rows for this year)

2020 —> 780 mm (the SUM of all the rows for this year)

2021 —> 800 mm (the SUM of all the rows for this year)

Result I would like to get:

**Annual average = 810 mm**

Data:

March 19 —> 200 mm (the SUM of all the rows for this month and year)

March 20 —> 170 mm (the SUM of all the rows for this month and year)

March 21 —> 150 mm (the SUM of all the rows for this month and year)

Result I would like to get:

**Historic average of actual month (March) = 173.33 mm**

This would be an idea for the Annual average, but is hardcoded and ony for the fixed years included in the formula (in this case 3 years included):

AVERAGE ( LIST ( SUM ( SELECT ( Datos[Diferencia (mm)], YEAR ([Fecha]) = YEAR (TODAY()) )), SUM ( SELECT ( Datos[Diferencia (mm)], YEAR ([Fecha]) = YEAR (TODAY()) -1 )), SUM ( SELECT ( Datos[Diferencia (mm)], YEAR ([Fecha]) = YEAR (TODAY()) -2 )) ))

This would be an idea for the Historic average of actual month, but is hardcoded and ony for the fixed years included in the formula (in this case 3 years included):

AVERAGE ( LIST ( SUM ( SELECT ( Datos[Diferencia (mm)], EOMONTH ([Fecha], 0) = EOMONTH (TODAY(), 0))), SUM ( SELECT ( Datos[Diferencia (mm)], EOMONTH ([Fecha], 0) = EOMONTH (TODAY(), -12))), SUM ( SELECT ( Datos[Diferencia (mm)], EOMONTH ([Fecha], 0) = EOMONTH (TODAY(), -24)))))

Do anyone have an idea of how to do this on an efficient way?

Thanks.