Measure for average in a time period also returning a value for dates outside the time period

[dblair]

This is driving me  little crazy - I'm trying to measure a rolling average only in a period 12 months prior to a given date.

I'm getting the correct values for the correct months, but for some reason it's also returning some odd value (an average over the whole selected period?) for all other months as well.

I copied this pretty closely from another measure (12-week rolling average of [measure] for 12 weeks prior to selected date), and that one  returns blanks for any dates outside the period. I've compared them closely, but I have no idea why they behave differently.

Here are the measures:

12-Month Average Files Opened =

CALCULATE(

DIVIDE(

[Files Opened],

DISTINCTCOUNT('Calendar'[CurMonthOffset]),

BLANK()

),

FILTER(

ALL('Calendar'),

'Calendar'[CurMonthOffset] >= max('Calendar'[CurMonthOffset])-12

&& 'Calendar'[CurMonthOffset] <= max('Calendar'[CurMonthOffset])

)

)

12m Average Files Opened 12m prior to selection =

CALCULATE(

[12-Month Average Files Opened],

FILTER(

'Calendar',

'Calendar'[CurMonthOffset] <= [Selected Month]

&& 'Calendar'[CurMonthOffset] >= [Selected Month] - 12

)

)

And here's a screenshot of the values:

[Icey · Accepted solution]

Hi @dblair ,

Have tried something like below?

12m Average Files Opened 12m prior to selection =
VAR FilesOpened_Sum_ =
    CALCULATE (
        [Files Opened],
        DATESINPERIOD ( 'Calendar'[Date], MAX ( 'Calendar'[Date] ), - 12, MONTH )
    )
RETURN
    DIVIDE ( FilesOpened_Sum_, 12 )

Reference: Display Last N Months & Selected Month using Single Date Dimension in Power BI – Some Random Thought...

Best Regards,

Icey

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[dblair]

Replacing my "12-Month Average Files Opened" Measure with this formula caused my "12m Average 12 motnhs prior to the selected month" measure to start working!
Still don't know why, but thanks!