Rolling N months average + filter/selection

[joeallen]

Hello

I need help calculating 3 month average. It needs to work dynamically with selections and filter on date fields like YearMonth. Please see explanation below

Note: YearMonth has been generated from a date field (YYYY-MM-DD) so date field is available

YearMonth	Sales	3M sales AVG
201709	5	5
201710	10	7,5
201711	4	6,3
201712	5	6,3
201801	2	3,6
201802	5	4
201803	10	5,6
201804	10	5

Expressions:

Sales = sum (sales)

3M sales AVG = rangeavg(above(sum(sales),0,3))

Now if I filter on a YearMonth for example 201804, I get same value in "3M sales AVG" column as I get in "Sales" column

I get for 201804

Sales	3M sales AVG
10	10

But I need it to be:

Sales	3M sales avg
10	5

So same for other months for example 201801 I get 2+2 but I need 2+3.6...

I guess this is due to filtering on a YearMonth makes the rangeavg expression not work since it doesn't have access to the previous periods... so is there a solution for this? Preferably in expression itself. Or is an AsOf calendar required?

[joeallen]

Note a little mistake 3M sales avg for 201804 should be 8.3 obviously not 5 (sorry)