Hi Jesse,

The attached workbook shows a solution to get average monthly sales by Product Category using the WINDOW_AVG table calculation function.

Cheers,

Tom

You could also use an LoD.

First step is to create a field that defines the Month and Year. Here are some options.

1. Create Custom Date Field

2. Create a calculated field that basically does the same thing

(DATEPART('year', [Order Date])*100 + DATEPART('month', [Order Date]))

3. Use Truncation

DATETRUNC('month', [Order Date])

Either of these will work, and they all give you a field to use in an LoD, i.e. Year and Month (2015-12)

Once you have that, you can just reference it using an INCLUDE LoD

AVG({ INCLUDE [Order Date (Month / Year)] : SUM([Sales]) })

Since you are using an LoD, you don't need to worry about adding a LAST() function, or adding the Order Date to the Detail shelf.  And you can filter, without issues.

Regards,

Rody