Grouped moving average for defined

Question

Hi All,&nbsp;I have a table like this&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;CountriesCitiesYearsTemperatureCountry1City1Year1Temperature(value)Country1City1Year2Temperature(value)Country1City1Year3Temperature(value)Country1City2Year1Temperature(value)Country1City2Year2Temperature(value)Country1City2Year3Temperature(value)Country2City3Year1Temperature(value)Country2City3Year2&nbsp;Temperature(value)Country2City3Year3&nbsp;Temperature(value)Country2City4Year1&nbsp;Temperature(value)&nbsp;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.&nbsp;Is there any solution that somehow strictly calculates inside the boundaries?&nbsp;The intended solution would look like this for a 2-year moving averageCountriesCitiesYearsTemperatureTemp Moving AverageCountry1City1Year1Temperature(value)-Country1City1Year2Temperature(value)M.AVG1Country1City1Year3Temperature(value)M.AVG2Country1City2Year1Temperature(value)-Country1City2Year2Temperature(value)M.AVG1Country1City2Year3Temperature(value)M.AVG2Country2City3Year1Temperature(value)-Country2City3Year2&nbsp;Temperature(value)M.AVG1Country2City3Year3&nbsp;Temperature(value)M.AVG2Country2City4Year1&nbsp;Temperature(value)-&nbsp;I would be very grateful even if someone only pointed out the problem or directed me in the right direction.&nbsp;

tamerj1 · Accepted Answer

Hi&nbsp;Anonymous&nbsp;Please try&nbsp;Temp Moving Average =
CALCULATE (
    AVERAGE ( TableName[Temperature] ),
    ALLEXCEPT ( TableName, TableName[Countries], TableName[Cities] ),
    TableName[Years] &lt;= MAX ( TableName[Years] )
)&nbsp;

Forum Discussion

Grouped moving average for defined

1 Reply

Related content

un define

Fabric & Azure Data User Group Program Requirements & Benefits

Grouped Average

average by group

Average by group

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)