Calculating a 3 month average

[itechC]

Hello,

I have a report with monthly trends being displayed in the [reporting month] footer.I am also calculating a grand total in the report footer. In the report footer i would like to calculate the average of only the LAST 3 months. How would i go about doing this when i am displaying more then 3 months in my report.

 

[lameid]

The easiest way is to make a control with the max of the month field in the report header and make it not visible.

Then in the report footer use that value, dateadd, sum and IIF to conditionally sum and count.

=Sum(IIF(MonthDate between Dateadd("m",-3,txtMaxDate) And txtMaxDate,ValueToSum,0))

You can do a similar thing for count and use the 2 to calculate the average.

I made some assumptions about your data and what you are trying to do, so it is possible that this is not the right answer for you.