Its not just weekdays. I'm trying to get the average sales of Mondays for the past 90 days and compare it to Yesterday average sales. Same with Tuesday, Wednesday, ect.
So if I have a sales person list of sales for the past year I want to be able to compare each Monday to the past 90 days of Mondays. Same thing with the rest of the days of the week.
The only thing I came up with is doing some funky stuff on the back end that compares multiples of 7 for the past 7 days while today()-CalendarDate < 91, then I mapped it to the data set. That adds 10 mins to the load time and I was trying to come up with another way.
This would work if I was only comparing Mondays, but I'm comparing Yesterday or all the days in the last Quarter to the 90 day average.
We have high performing days and low performing days and its not fair to compare a high performing day to a low performing day and the average of the two doesn't give us much of a comparison either. So comparing each day to itself is what we are looking for but its proving difficult to figure out.
Final goal is to be able to look at past 30 to 90 days worth of sales per AE() per Customer and compare those days to the Average over the past 90 days. I'll be displaying it in a Pivot table and if the sales are down 10% or 25% then the Cell will be highlighted. Then they can look into that day to see where the AE can improve.
Most of the time they are looking at yesterday, but they could be looking at the past 90 days or even more if they wanted too.
I am trying to come up with a way to display the average 90 day per day of the week. Does that make sense?