cancel
Showing results for
Did you mean:

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

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

#### Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

#### Power BI Monthly Update - August 2024

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

#### Microsoft Fabric & AI Learning Hackathon

Learn from experts, get hands-on experience, and win awesome prizes.

#### Fabric Community Update - September 2024

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

Top Solution Authors
Top Kudoed Authors