Forum Discussion

gilmore_staci's avatar
gilmore_staci
Helper II
6 months ago
Solved

moving average help

I'm trying to add a Moving 3 month average to my Line Chart.  The field being used for the x-axis is a text field so I can just see Month-Year.  I put the Date that the text came from in the calculat...
DAX
  • V-yubandi-msft's avatar
    V-yubandi-msft
    6 months ago

    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....

gilmore_staci's avatar
gilmore_staci
Helper II
6 months ago

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 _result

 

 

V-yubandi-msft's avatar
V-yubandi-msft
Community Support
6 months ago

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....