I've been asked to display the most recent 6 months, and compare the most recent 6 month average to the previous years same 6 month average. I've been able to isolate the dates by using the following formula
[Date] >= dateadd('mm',-6,DateTimeNow()) <-- This is used in a cross table, which averages a "Booked/Available" field
However, I'm not sure how to calculate the average of the previous years 6 months.
I can isolate the dates by using this formula
[Date] <= dateadd('mm',-13,DateTimeNow()) AND [Date] >= dateadd('mm',-18,DateTimeNow())
How would I approach this to figure out what the difference is from the most recent 6 months with the Booked/Available field?
Average last six months:
Sum(if([Date] >= dateadd('mm',-6,DateTimeNow()), [ColToAvg],0))/Sum(if([Date] >= dateadd('mm',-6,DateTimeNow()), 1,0))
Average previous six months:
Sum(if([Date] <= dateadd('mm',-13,DateTimeNow()) AND [Date] >= dateadd('mm',-18,DateTimeNow()), [ColToAvg],0))/Sum(if([Date] <= dateadd('mm',-13,DateTimeNow()) AND [Date] >= dateadd('mm',-18,DateTimeNow()), 1,0))
Difference between two averages:
Sum(if([Date] >= dateadd('mm',-6,DateTimeNow()), [ColToAvg],0))/Sum(if([Date] >= dateadd('mm',-6,DateTimeNow()), 1,0)) - Sum(if([Date] <= dateadd('mm',-13,DateTimeNow()) AND [Date] >= dateadd('mm',-18,DateTimeNow()), [ColToAvg],0))/Sum(if([Date] <= dateadd('mm',-13,DateTimeNow()) AND [Date] >= dateadd('mm',-18,DateTimeNow()), 1,0))