Calculate moving annual totals (MAT) - month dimension

[#1]

Hello all,

On the attached Combo Chart I have the requirement to add the moving annual totals (pink line) so that in Jan it would show the cumulative values from Feb'14 to Jan'15. In Feb it would show the cumulative values from March'14 to Feb'15 and so on. I have seen several examples of MAT but in all of them the chart dimension always includes Month-Year instead of only Month as per my requirement. Has anyone come across a similar task? any ideas would be very appreciated.

Thanks

[#2]

Hello,

I eventually came up with the solution for this requirement. I will post it here just in case someone if facing a similar challenge. Following the formula I used in the chart expression:

MAT = Total Last Year Sales + This year cumulative sales (YTD) - Last year cumulative sales (LYTD)

SUM({ $<Year={$(=MAX(Year)-1)}>}TOTAL sales) +

(RangeSum(Above(SUM({ $< Year={$(=MAX(Year))}>} sales), 0, RowNo())))-

(RangeSum(Above(SUM({ $< Year={$(=MAX(Year)-1)}>}sales), 0, RowNo())))

Regards

[itec_pao]

Hi all,

I have come across a solution, in order to provide a good moving annual total (MAT) analysis. The solution is called As-Of table. A very good example is provided here by Henric Conström:

The As-Of Table

Additionally, by using DateDiff (difference between the two dates), you might create different flags for flagging current 3months, 6months, 1Year, Ytd, etc.

Regards,

Oktay