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 understand what needs to be done, but in order to get my original data, the x-axis date is coming from my Case table. If I swtich and model the date to the Date in my Calendar table, my original data all ends up as either zero's or I end up getting every single day, not a combined month data. I will post pics.
Hi gilmore_staci ,
Based on the screenshots, it seems the relationship between your Case date and Calendar date is inactive. This causes the visual to display 0s when you set the X-axis to the Calendar table, as it doesn’t filter the Case table correctly.
Try these steps.
1. Verify that the relationship between Case[Date Incident Resolved] and Calendar[Date] is active.
2. Use a Month Year column from the Calendar table for the X-axis instead of a text field from the Case table.
3. Note that using the full Date field will show daily values in Power BI, so a Month-Year column will keep it grouped by month.
Once the relationship is active and the axis is set from the Calendar table, your monthly values and 3 month moving average should display correctly.
Thanks for sharing more details and your insights.
V-yubandi-msft thank you, I'm getting closer. The moving average is giving zero for the data points but the line seems correct. The only thing I can see is the AVERAGE in the calculation. If I use AVERAGE it says I can't use Dealer Resolution Rate its either not in the table or can't be used in the expression. It doesn't give an error if I use AVERAGEX, but also gives me zeros
Dealer Resolution Rate(percentage) =
IFERROR('Case'[FalseCount]/'Case'[Count of incidents resolved in Month], 0)Moving_Average_3_Months =VAR _end = MAX('Case'[Date Incident Resolved])VAR _start = EDATE(_end, -3)VAR _result =CALCULATE(AVERAGEX('Case', 'Case'[Dealer Resolution Rate]),'Case'[Date Incident Resolved]>_start && 'Case'[Date Incident Resolved]<=_end,ALLEXCEPT('Case', 'Case'[Date Incident Resolved]))RETURN _resultHi 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....