Previous Month Reference Line

[#1]

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?

[oknotsen]

I am with Sunny T on this one:

I fear we can not help you unless you give us some sample data to look at.

[#3]

Here you go, hope you can see what I am trying to do.

[antoniotiman]

Hi Daniel,

try

AVG({<Date={'>=$(=MonthStart(Max(Date),-1))<=$(=MonthEnd(Max(Date),-1))'}>} [Holiday Overall])

Regards,

Antonio

[#5]

Hi Antonio,

This works, but only when no selection is made. If you make a selection the reference line disappears.

I guess the sample data has not been helpful?

[antoniotiman · Accepted solution]

Try to add State 1

AVG({1<Date={'>=$(=MonthStart(Max(Date),-1))<=$(=MonthEnd(Max(Date),-1))'}>} [Holiday Overall])

[#7]

Thanks Antonio,

That worked, thank you.

The next question is, if I want a gauge that shows an average of multiple scores, would I just add them on?

AVG({1<Date={'>=$(=MonthStart(Max(Date),-1))<=$(=MonthEnd(Max(Date),-1))'}>} [Holiday Overall]+[Food Quality])

[sunny_talwar]

May be like this:

Avg({1<Date={'>=$(=MonthStart(Max(Date),-1))<=$(=MonthEnd(Max(Date),-1))'}>} RangeAvg([Holiday Overall], [Food Quality]))