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

Announcements

#### Power BI September 2023 Update

Take a look at the September 2023 Power BI update to learn more.

#### Learn Live: Event Series

Join Microsoft Reactor and learn from developers.

#### Exclusive opportunity for Women!

Join us for a free, hands-on Microsoft workshop led by women trainers for women where you will learn how to build a Dashboard in a Day!

#### Power Platform Conference-Power BI and Fabric Sessions

Join us Oct 1 - 6 in Las Vegas for the Microsoft Power Platform Conference.

Top Solution Authors
Top Kudoed Authors