Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
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

City1Year2Temperature(value)

Country1

City1Year3Temperature(value)

Country1

City2Year1Temperature(value)

Country1

City2Year2Temperature(value)

Country1

City2Year3Temperature(value)

Country2

City3Year1Temperature(value)
Country2City3Year2 Temperature(value)
Country2City3Year3 Temperature(value)
Country2City4Year1 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

TemperatureTemp Moving Average

Country1

City1

Year1

Temperature(value)-

Country1

City1Year2Temperature(value)M.AVG1

Country1

City1Year3Temperature(value)M.AVG2

Country1

City2Year1Temperature(value)-

Country1

City2Year2Temperature(value)M.AVG1

Country1

City2Year3Temperature(value)M.AVG2

Country2

City3Year1Temperature(value)-
Country2City3Year2 Temperature(value)M.AVG1
Country2City3Year3 Temperature(value)M.AVG2
Country2City4Year1 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
tamerj1
Super User
Super User

Hi @Anonymous 
Please try

 

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

 

View solution in original post

1 REPLY 1
tamerj1
Super User
Super User

Hi @Anonymous 
Please try

 

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

 

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

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

Top Solution Authors