topic Re: Measure for average in a time period also returning a value for dates outside the time period in DAX Commands and Tips

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

dblair — Mon, 18 Oct 2021 13:59:26 GMT

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:

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

Icey — Thu, 21 Oct 2021 08:11:14 GMT

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 Thoughts (sqljason.com)

Best Regards,

Icey

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

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

dblair — Thu, 21 Oct 2021 12:48:37 GMT

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!