We help IT Professionals succeed at work.

# Moving 3 month moving average with Year and Month numbers only

on
I am looking to calculate the 3 month moving average but I only have the year and the month numbers (as separate fields in the table) for each monthly spend (dollars).

Data looks something like this:

ReceiptYear            ReceiptMonth              Spend (just some random numbers for this example)
2009                               7                              5
2009                             8                             8
2009                             9                             5
2009                             10                             6
2009                             11                             3
2009                             12                             6
2010                             1                             8
2010                             2                             2
2010                             3                             6
2010                             4                              4
2010                             5                             7
2010                             6                             1
2010                             7                             6
2010                             8                             9

I figured out how to make a date out of the year and month fields:
ReceiptDate: DateSerial([ReceiptYear],[ReceiptMonth],1)
which gives me dates for the first of the month which is fine.

but can't seem to figure out how to get the moving average part working.
Thanks.
Comment
Watch Question

## View Solutions Only

Top Expert 2016
Commented:

ReceiptDate: DateSerial([ReceiptYear],[ReceiptMonth],1)

Criteria   Between dateserial(year(date()),month(date()),1) and dateserial(year(date()),month(date())-3,1)

Commented:
Capricorn,

Also, how do I get the average?

Top Expert 2016
Commented:

do you have records that have the year 2011 and months  9,10,11

Commented:
Oops. I didn't have 2011 data.

I inserted 1-11 months for 2011 Year and am getting 9/1-11/1 (3 records).

How do I calculate the moving average for those 3 months?
Also, how can I loop through this to calculate the moving average for all of 2012?
(I know this is building a house of cards, but this is the way I need to do it)

Commented:
I answered my own question right above with this SQL:

SELECT DISTINCT Avg([spend]) AS AverageSpend
FROM Data
WHERE (((DateSerial([ReceiptYear],[ReceiptMonth],1)) Between DateSerial(Year(Date()),Month(Date()),1) And DateSerial(Year(Date()),Month(Date())-3,1)));

But how can I loop through to roll this forward for 12 months? (building on the previous month's calculated 3 month average)
MIS Liason
Most Valuable Expert 2012

Commented:
First, there is no one set "Moving Average" formula.
A moving average can be calculated in any number of ways.
They range from simple:
http://chandoo.org/wp/2009/04/28/calculate-moving-average/
...to more complex:
http://www.fourmilab.ch/hackdiet/www/subsection1_4_1_0_8.html

So you will have to be very specific on exactly what you want here.

If what you really need is a simple "Cumulative" ("running") average you can do something like this:

JeffCoachman

untitled.JPG
Access-EEQ27480258-ReportMovingC.mdb
Top Expert 2016

Commented:

this criteria

Between DateSerial(Year(Date()),Month(Date()),1) And DateSerial(Year(Date()),Month(Date())-3,1)));

gets you the rolling three months records..

now what do you mean by

<But how can I loop through to roll this forward for 12 months? (building on the previous month's calculated 3 month average)>

Commented:
Capricorn,
My question can be illustrated with the screenshoot from boaq2000:

Let's pretend today is Jan 1 2011 on his data table:
I want to forecast the 3 month rolling average out until Aug 2011.
This is easy enough based on ACTUAL [prior to today] data, but I want to continue it out 8 months until Aug 2011.
This would mean that the rolling average in the future months is based on the other rolling averages from any future months
for example:
July 2011 is based on June, May, April, but those months are also based on 'future' calculated rolling averages.

What I'm trying to do is this:
Each new month, I need to fill in a monthly "forecast" that goes out until the end of the year.

This is at it's worst in January as I am predicting 12 months, but as the year moves along, more of the "spend" is based on actual (<now), and the forecast is only for the remaining months of the year (>now).

Commented:
boaq2000,

How can I change your [txtAmountRunSum]/[txtRecCount] formula into a query that will  populate a table that will fill in the "rest of the months" of the year going forward
MIS Liason
Most Valuable Expert 2012

Commented:
Your original question did not say how you wanted to display this data...

You will have to investigate one of the many techniques to do "running values" in queries.

See here for some hints
http://support.microsoft.com/kb/290136
Top Expert 2016

Commented:
i believe the original question of this thread has been resolved...

post another q for your other problems..

Commented: