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.
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.
Solved! Go to Solution.
Hi @Anonymous
Please try
Temp Moving Average =
CALCULATE (
AVERAGE ( TableName[Temperature] ),
ALLEXCEPT ( TableName, TableName[Countries], TableName[Cities] ),
TableName[Years] <= MAX ( TableName[Years] )
)
Hi @Anonymous
Please try
Temp Moving Average =
CALCULATE (
AVERAGE ( TableName[Temperature] ),
ALLEXCEPT ( TableName, TableName[Countries], TableName[Cities] ),
TableName[Years] <= MAX ( TableName[Years] )
)
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
47 | |
24 | |
20 | |
15 | |
13 |
User | Count |
---|---|
51 | |
46 | |
39 | |
19 | |
19 |