Avatar of ovelove

asked on 

3 Month Moving average with three criterias

Looking for a way to calculate a three month average with three criterias (Transaction date, Area and product id).

Current code is: MovingAverage: DAvg("Sales","Table1","Area ='" & [Area] & "' AND Month(format(TransactionDate,'mm/dd/yyyy') <" & (Month(Format([TransactionDate],'mm/dd/yyyy')+2)\3))

Struggeling with the Transaction date as I only receive an error in the query (the data is only monthly and all data is therefor set to 1/1/2015 for Jan and 2/1/2015 for feb).

I would also need product ID to be part of the criteria for the three month moving average.

Any suggestions?  I've attached the test database as well.
Microsoft Access

Avatar of undefined
Last Comment

8/22/2022 - Mon