cancel
Showing results for
Did you mean:

Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.

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] )
)``````

Announcements

#### Microsoft Fabric Learn Together

Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City

#### Power BI Monthly Update - April 2024

Check out the April 2024 Power BI update to learn about new features.

#### Fabric Community Update - April 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors
Top Kudoed Authors