I am having a similar issue where I am trying to compare daily average to an average over past 3 years including today.
i am using the following formula to calculate the difference between two average and was leaving it upto to the filters i.e. date to execute the formula over right number of records.
AVG([Si Delay]) - AVG([3Y Past Delay Interim])
However, I do not think this is working. Any thoughts? Once the formula works I need to color code based on the result i.e. +ve or -ve.
Are you going to show the months on the viz, or do you want to only calculate the monthly average?
Only calc the monthly average so i can use it with other calculated fields.
So this was a three-step process for me--there's probably more elegant ways to do this. I used the Sample - Superstore data.
In this last screenshot, you can see that the average monthly sales of $47,858 is showing on each Label, so if you added different fields to the viz, the last calculation will remain the same.
Hope this helps..