cancel
Showing results for
Did you mean:
Anonymous
Not applicable

## Grouped moving average for defined

Hi All,

I have a table like this

 Countries Cities Years Temperature Country1 City1 Year1 Temperature(value) Country1 City1 Year2 Temperature(value) Country1 City1 Year3 Temperature(value) Country1 City2 Year1 Temperature(value) Country1 City2 Year2 Temperature(value) Country1 City2 Year3 Temperature(value) Country2 City3 Year1 Temperature(value) Country2 City3 Year2 Temperature(value) Country2 City3 Year3 Temperature(value) Country2 City4 Year1 Temperature(value)

I would like to calculate the moving average temperature by City and Country. All of my previous attempts have slipped through the Country/City groups meaning that moving averages contained data from previous cities and years.

Is there any solution that somehow strictly calculates inside the boundaries?

The intended solution would look like this for a 2-year moving average

 Countries Cities Years Temperature Temp Moving Average Country1 City1 Year1 Temperature(value) - Country1 City1 Year2 Temperature(value) M.AVG1 Country1 City1 Year3 Temperature(value) M.AVG2 Country1 City2 Year1 Temperature(value) - Country1 City2 Year2 Temperature(value) M.AVG1 Country1 City2 Year3 Temperature(value) M.AVG2 Country2 City3 Year1 Temperature(value) - Country2 City3 Year2 Temperature(value) M.AVG1 Country2 City3 Year3 Temperature(value) M.AVG2 Country2 City4 Year1 Temperature(value) -

I would be very grateful even if someone only pointed out the problem or directed me in the right direction.

1 ACCEPTED SOLUTION
Super User

Hi @Anonymous

Temp Moving Average =
CALCULATE (
AVERAGE ( TableName[Temperature] ),
ALLEXCEPT ( TableName, TableName[Countries], TableName[Cities] ),
TableName[Years] <= MAX ( TableName[Years] )
)

Super User

Hi @Anonymous

Temp Moving Average =
CALCULATE (
AVERAGE ( TableName[Temperature] ),
ALLEXCEPT ( TableName, TableName[Countries], TableName[Cities] ),
TableName[Years] <= MAX ( TableName[Years] )
)