Accumulated value of the last 12 months and of the previous period

[kira_whopper]

Hi there,

I'm willing to calculate the value accumulated of the last 12 months for the 13 months displayed in a chart, for example:

Apr-2015 -> Sums from Apr-2015 until May-2014

Mar-2015 -> Sums from Mar-2015 until Apr-2014

.

.

.

Apr-2014 -> Sums from Apr-2015 until May-2013

Ok, that I got figured out with the following formula:

if(Only({<

YearMonth ={">=$(=Date(addmonths(Max(YearMonth), -12), 'MMM-YY'))<=$(=Date(addmonths(Max(YearMonth), -1),  'MMM-YY'))"},

Year=,

Month=>} YearMonth),

RangeSum

(

Above

(

Sum( {<

YearMonth ={">=$(=Date(addmonths(Max(YearMonth), -23), 'MMM-YY'))<=$(=Date(addmonths(Max(YearMonth), -1),  'MMM-YY'))"},

Year= ,

Month= >}

  Value)

,0,12)

)

,

RangeSum

(

Above

(

Sum({<

YearMonth ={">=$(=Date(AddMonths(Max(YearMonth), -12), 'MMM-YY'))<=$(=Date(Max(YearMonth), 'MMM-YY'))"},

Year=,

Month=>}

  Value)

,0,12)

)

)

The Only within the IF condition, is for the calculation of the months outside the period of 13 months.

This works like a charm, but my problem now is to calculate the previous period, wich would be, in the same example:

Apr-2014 -> Sums from Apr-2014 until May-2013

Mar-2014 -> Sums from Mar-2014 until Apr-2013

.

.

.

Apr-2013 -> Sums from Apr-2013 until May-2012

I tryed using a AddYears(-1) in the YearMonth set analysis, but it didn't worked.

Any ideas?

[Gysbert_Wassenaar]

See this document: Calculating rolling n-period totals, averages or other aggregations

[kira_whopper · Accepted solution]

A managed to do it without changing the script.

I modified the Abobe (from 0,12 to 12,12) and the YearMonth shift (from -23 and -12 to -35 and -24).

This is the new formula:

if(Only({<YearMonth ={">=$(=Date(addmonths(Max(YearMonth), -12), 'MMM-YY'))<=$(=Date(addmonths(Max(YearMonth), -1),  'MMM-YY'))"}, Year=, Month=>} YearMonth),

RangeSum

(

Above

(

Sum( {<

YearMonth ={">=$(=Date(addmonths(Max(YearMonth), -35), 'MMM-YY'))<=$(=Date(addmonths(Max(YearMonth), -1),  'MMM-YY'))"},

Year= ,

Month= >}

Value)

,12,12)

)

,

RangeSum

(

Above

(

Sum({<

YearMonth ={">=$(=Date(AddMonths(Max(YearMonth), -24), 'MMM-YY'))<=$(=Date(Max(YearMonth), 'MMM-YY'))"},

Year=,

Month=>}

  Value)

,12,12)

)

)