Forum Discussion
moving average help
Hi gilmore_staci ,
Thank you for sharing the formula. The error with AVERAGE occurs because Dealer Resolution Rate is a measure, not a column. AVERAGE works only with columns, so Power BI doesn’t allow it here. This is also why AVERAGEX doesn’t return an error but gives zeros, as it iterates row by row while your percentage is already calculated as a measure.
Since the value is already aggregated, there’s no need to average it again over the Case table. Instead, the moving average should evaluate the measure over the last 3 months using the date context, allowing Power BI to calculate the measure across the previous 3 months based on the date filter, rather than averaging row-level values, which can lead to zeros.
Hope this helps....
hi gilmore_staci ,
Time intelligence functions (like DATESINPERIOD, LASTDATE ) rely on dedicated date table.
Supposing you have a data table like below:
| date | rate |
| 2025/1/1 | 0.1 |
| 2025/2/1 | 0.12 |
| 2025/3/1 | 0.14 |
| 2025/4/1 | 0.16 |
| 2025/5/1 | 0.18 |
| 2025/6/1 | 0.2 |
| 2025/7/1 | 0.22 |
| 2025/8/1 | 0.24 |
| 2025/9/1 | 0.26 |
| 2025/10/1 | 0.28 |
| 2025/11/1 | 0.3 |
You may try the following:
1) write a calculated table like:
dates =
ADDCOLUMNS(
CALENDAR(MIN(data[date]), MAX(data[date])),
"YY/MM", FORMAT([Date], "YY/MM")
)2) relate data[date] with the dates[date]
3) plot an chart/table visual with dates[yy/mm] and a measure like:
3M Avg Moving =
VAR _date = MAX(dates[date])
VAR _result =
CALCULATE(
AVERAGE(data[rate]),
DATESINPERIOD(dates[Date], _date, -3, MONTH)
)
RETURN _resultit works like:
Or try without Time Intelligence Functions, like:
1) write a calculated column in data table like:
YY/MM = FORMAT([date], "YY/MM")2) plot a visual with data[yy/mm] with a measure like:
3M Avg Moving NonTIF =
VAR _end = MAX(data[date])
VAR _start = EDATE(_end, -3)
VAR _result =
CALCULATE(
AVERAGE(data[rate]),
data[date]>_start && data[date]<=_end,
ALLEXCEPT(data, data[date])
)
RETURN _result
it works like: