Gauge to display Last month data

[#1]

Dear Community:

Current month is October, but my Search data is one month lapse, which mean i have search data up to August only.

May I know how to display Max(Month) which is August data?

Current expression with error:

=sum({<[Date]={"$(=Date(AddMonths((Date),-1),'DD/MM/YYYY'))"},Company={'ABC'},Year=,Quarter=,Month=,Week=>} [Search Volume])

/sum({<[Date]={"$(=Date(AddMonths((Date),-1),'DD/MM/YYYY'))"},Year=,Quarter=,Month=,Week=>} [Search Volume])

Previously i use variable as varMonthStart =MonthStart(Max(Date)) with expression as shown below:

=sum({<[Date]={"$(=varMonthStart)"},Company={'ABC'},Year=,Quarter=,Month=,Week=>} [Search Volume])

/sum({<[Date]={"$(=varMonthStart)"},Year=,Quarter=,Month=,Week=>} [Search Volume])

But this will show September data which the data is not available yet...

Anyone can help to twist the expression?

Thanks and best regards,

Chanel

[#2 · Accepted solution]

Please find the below MTD Exp:

Previous MTD

sum({$<Year = {$(=max(Year))}, Month = {"<=$(=max({<Year={$(=max(Year))}>} Month-1))>$(=max({<Year={$(=max(Year))}>} Month)-2)"}>} Amount)))

Current MTD

(sum({$<Year = {$(=max(Year))}, Month = {"<=$(=max({<Year={$(=max(Year))}>} Month))>$(=max({<Year={$(=max(Year))}>} Month)-1)"}>} Amount))

[senpradip007]

Hi,

You check the date formate.

[#4]

Hi,

Set variable for max month with max date of August.

[#5]

Awesome!!!! It's working! Thanks very muchie!!!