Average of the previous three months

[jerryr125]

Hi - 

I have quantity data. I would like to do the following once a month/year is selected (filtered) :

- Take an average of that month and the previous three months

- Display that value in a KPI

I tried this and no luck:

Avg({$<[SalesDate.autoCalendar.YearMonth]={">$(=AddMonths(Max([SalesDate.autoCalendar.YearMonth]),-3,1))<=$(=Max([SalesDate.autoCalendar.YearMonth]))"}>}QuantitySold)

thoughts ? Jerry

[marpet]

I would like to use a Date field.

Avg({<[SalesDate.autoCalendar.Date]={">$(=MonthStart(AddMonths(Max([SalesDate.autoCalendar.Date]),-3)))<=$(=Max([SalesDate.autoCalendar.Date]))"}, [SalesDate.autoCalendar.Year]=, [SalesDate.autoCalendar.Month]=>}[QuantitySold])

[jerryr125]

Hi - unfortunately that did not work.

Example - if I select the following months:

Feb 2016 : Quantity Sold 3,157

May 2016 : Quantity Sold 11,379

April 2016 :Quantity Sold 11,609

The three month average should equal = 8,715

It is displaying: 3.25

thoughts ?

[jerryr125]

Hi I tried this expression as well.

It seems to be pulling the correct timeframe BUT not the displaying the average for the last three months (by month).

Thoughts ? Jerry

Avg({$<[SalesDate.autoCalendar.YearMonth]={">$(=AddMonths(Max([SalesDate.autoCalendar.YearMonth]),-3,1))<=$(=AddMonths(Max([SalesDate.autoCalendar.YearMonth]),1,1))"}>}QuantitySold)

[jerryr125]

this does the trick :

(Sum({$<[SalesDate]={">=$(=AddMonths(Max([SalesDate.autoCalendar.YearMonth]),-2,1))<$(=AddMonths(Max([SalesDate.autoCalendar.YearMonth]),1,1))"}>}QuantitySold))/3