This month 12 months ago

[aaronnayan]

HI Guys,

Hoping you could help me.

I want to avg(sales) for the current month 12 months ago so July 2017

I already have a current month flag field which returns 1 or 0,  (today's month- July 2018.)

I want to get the avg(sales) for the current month, 12 months ago.

I have already tried this but its not working:

AVG({<MonthYear={"$(=Monthname(addmonths(Max(MonthYear),-12)))"}>}Sales)

Could i please request to try do this in the front end if possible

Thanks,

Aaron

[Gysbert_Wassenaar]

How is the MonthYear field created? It should be a date, but not just a string value. Otherwise max(MonthYear) won't return a date value that can be calculated with in the addmonths function.

You could check if Num(MonthYear) returns a number.

[aaronnayan]

Hi Gysbert

I have another Date field that I am using called DateKEY. This can be turned into a Number with the NUM()

I am able to get July 2017 with this expression

Monthname(addmonths(Max([DateKEY),-12))

However i cant get the average of monthly sales in there

What would you propose?

Thanks,

Aaron