I would like to know how to define a metric to calculate 12 months moving average of a certain fact including current month

Question

I would like to know how to define a metric to calculate 12 months moving average of a certain fact including current month. The metric I defined as 'SELECT AVG('FACT') WHERE Month/Year(Date) BETWEEN {This - 12} AND {This}' hadn't worked expectedly but displayed natural averages for each month.

Answer 1

If you want this as just one number and not sliced by the month 'SELECT AVG('FACT') WHERE Month/Year(Date) BETWEEN {This}-12 AND {This}' should work (put the -12 after date macro {This}).