Moving average of a measure

[Anonymous]

Hi,

Apologies for raising an issue that's been raised many times before, but I've tried the solutions and they haven't worked for me.

I have a query with a big list of application numbers, their date, and the number of working days it took to process them. I have created a measure called "Percentage Ten Working Days or Under" that calculates the percentage of these apps processed in ten working days or under.

Percentage Ten Working Days or Under = CALCULATE(COUNTROWS('FACT-Applications'),'FACT-Applications'[WorkDays]<=10)/COUNTROWS('FACT-Applications')

I have also made a graph displaying this measure over time, by month based on my Date Table:

Now, I'd really like to run a six-month rolling average of this measure on this graph as well. I put in this DAX code based on another forum post:

Rolling Average of Percentage Ten Working Days or Under =
CALCULATE (
    AVERAGEX ('FACT-Applications', [Percentage Ten Working Days or Under]),
    DATESINPERIOD (
        'Calendar'[Date],
        LASTDATE ( 'Calendar'[Date]),
        -6,
        MONTH
 )
)

But it produced:

Which is clearly not right. What am I doing wrong? Sorry in advance but I won't be able to share the pbix because it has sensitive personal information in it.

[adambhappy · Accepted solution]

The AVERAGEX does a context transition to row level, i.e. for each single row in the fact table you are trying to average the result of the percentage measure which doesn't make much sense. Change the first parameter in the AVERAGEX() to your datetable and it should be fine.

Rolling Average of Percentage Ten Working Days or Under =
CALCULATE (
    AVERAGEX ('Calendar'[Date]', [Percentage Ten Working Days or Under]),
    DATESINPERIOD (
        'Calendar'[Date],
        LASTDATE ( 'Calendar'[Date]),
        -6,
        MONTH
 )
)

[Anonymous]

Cheers, adambhappy! I now understand the AVERAGEX function.