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
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))
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))
Hi,
You check the date formate.
Hi,
Set variable for max month with max date of August.
Awesome!!!! It's working! Thanks very muchie!!!