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] )
)
User | Count |
---|---|
88 | |
38 | |
36 | |
15 | |
14 |
User | Count |
---|---|
98 | |
31 | |
28 | |
20 | |
15 |