Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hello,
I have a Gauge that shows AVG(Score), and I want to add a reference line that shows the Avg(Score) from the previous month.
I am selecting Month from a Date field in the table using Month(Date).
I have tried =AVG({<Month={$(=Max(Month(Date))-1)}>} [Score]) but this is not working and gives me the current month score.
Any ideas?
Try to add State 1
AVG({1<Date={'>=$(=MonthStart(Max(Date),-1))<=$(=MonthEnd(Max(Date),-1))'}>} [Holiday Overall])
What is Month field format? Month(Date) is done in the script or front end filter object?
UPDATE: May be this:
=Avg({<Month={$(=Max(Month(Date))-1)}, Date>} [Score])
Hi Sunny,
That is again only giving me the same result as the current month. Both months have a different average.
The Month(Date) is a filter selection on the sheet.
Any other ideas?
Not unless you can share a sample to look at
I think the problem is that you are doing a selection on an expression value (Month(Date)); in this case the selection is applied to the underlying field (Date), so override the Date selection in the set expression:
=AVG({<Month={$(=Max(Month(Date))-1)}, Date>} [Score])
But you probably need:
=AVG({<Month={$(=Month(Max(Date))-1)}, Date>} [Score])
Hi Jonathan,
No that is still only giving me the current month as the reference line.
The page filter uses Month(Date) & ' ' & Year(Date) to create the selection list.
If I have July 2016 selected, and I use Max(Month(Date)-1 , it returns the value 6.
July = 7.
7 - 1 = 6.
6 = June.
Yes I know that.
That part of the code is giving the correct value, however when you combine it all together it gives the value for the current month not the previous month.