Moving 3 month moving average with Year and Month numbers only

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.
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Rey Obrero (Capricorn1)Commented:

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

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

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
SheaJeffAuthor Commented:
If I insert this criteria, I get no results.

Also, how do I get the average?
 screen shot
Rey Obrero (Capricorn1)Commented:
<If I insert this criteria, I get no results.>

do you have records that have the year 2011 and months  9,10,11
Newly released Acronis True Image 2019

In announcing the release of the 15th Anniversary Edition of Acronis True Image 2019, the company revealed that its artificial intelligence-based anti-ransomware technology – stopped more than 200,000 ransomware attacks on 150,000 customers last year.

SheaJeffAuthor 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)
SheaJeffAuthor Commented:
I answered my own question right above with this SQL:

SELECT DISTINCT Avg([spend]) AS AverageSpend
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)
Jeffrey CoachmanMIS LiasonCommented:
First, there is no one set "Moving Average" formula.
A moving average can be calculated in any number of ways.
They range from simple: more complex:

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:


Rey Obrero (Capricorn1)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)>

SheaJeffAuthor Commented:
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).
SheaJeffAuthor Commented:

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
Jeffrey CoachmanMIS LiasonCommented:
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
Rey Obrero (Capricorn1)Commented:
i believe the original question of this thread has been resolved...

post another q for your other problems..
SheaJeffAuthor Commented:
final solution for moving average of the next month was this:

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

I will post another question to clarify my original question about forecasting future values with the moving average.

Thanks for the help.
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.