Grouped moving average for defined

[Anonymous]

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. 

[tamerj1 · Accepted solution]

Hi @Anonymous 
Please try

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