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....
I think I'm missing something. In my visual I have
x-axis:
gilmore_staci if you want to use time intelligence functions: in the report you should use the "date" from a column of the Dimensional DateTable not from a column of the "Case" table.
You should have a DateTable to be used as a dimensional table: with a relationship one to many pointing the dates column in your transactional table.
Please see how what FreemanZ includes diferent tables in his first post: creating a DateTable; and proposed (in his second post) an option using the dates from your transactional table.
you dont need a measure to get the format of MM/YY to be used in the report, this calculated value won´t have a valid field with that calculated value in the table to iterate over that field...
please check FreemanZ second post.
Create a new CalculatedColumn inside your case table.
Use that CalculatedColumn (directly) in your report.
Use the next measure in your report:
MovingAvg_3M_CaseDates =
VAR _end = MAX('Case'[Date Incident Resolved])
VAR _start = EDATE(_end, -3)
VAR _result =
CALCULATE(
AVERAGE('Case'[Dealer Resolution Rate]),
'Case'[Date Incident Resolved]>_start && 'Case'[Date Incident Resolved]<=_end,
ALLEXCEPT('Case', 'Case'[Date Incident Resolved])
)
RETURN _result
If this response was helpful in any way, I’d gladly accept a kudo.
Please mark it as the correct solution. It helps other community members find their way faster.