Here's a member formula that might help. This assumes you want to include the current month in your rolling 12 months, as opposed to a trailing 12 months. It's not the most elegant solution in that it specifies each month . . . with some thought you could probably get this working in about five lines of code, but this is off the top of my head.
IF(@ISMBR("Jan"))
@SUMRANGE("Account_to_Sum", @CURRMBRRANGE("Months", LEV, 0, , 0)) +
@PRIOR("Account_to_Sum"->"Feb", -1, @LEVMBRS("Years",0)) +
@PRIOR("Account_to_Sum"->"Mar", -1, @LEVMBRS("Years",0)) +
@PRIOR("Account_to_Sum"->"Apr", -1, @LEVMBRS("Years",0)) +
@PRIOR("Account_to_Sum"->"May", -1, @LEVMBRS("Years",0)) +
@PRIOR("Account_to_Sum"->"Jun", -1, @LEVMBRS("Years",0)) +
@PRIOR("Account_to_Sum"->"Jul", -1, @LEVMBRS("Years",0)) +
@PRIOR("Account_to_Sum"->"Aug", -1, @LEVMBRS("Years",0)) +
@PRIOR("Account_to_Sum"->"Sep", -1, @LEVMBRS("Years",0)) +
@PRIOR("Account_to_Sum"->"Oct", -1, @LEVMBRS("Years",0)) +
@PRIOR("Account_to_Sum"->"Nov", -1, @LEVMBRS("Years",0)) +
@PRIOR("Account_to_Sum"->"Dec", -1, @LEVMBRS("Years",0));
ELSEIF(@ISMBR("Feb"))
@SUMRANGE("Account_to_Sum", @CURRMBRRANGE("Months", LEV, 0, , 0)) +
@PRIOR("Account_to_Sum"->"Mar", -1, @LEVMBRS("Years",0)) +
@PRIOR("Account_to_Sum"->"Apr", -1, @LEVMBRS("Years",0)) +
@PRIOR("Account_to_Sum"->"May", -1, @LEVMBRS("Years",0)) +
@PRIOR("Account_to_Sum"->"Jun", -1, @LEVMBRS("Years",0)) +
@PRIOR("Account_to_Sum"->"Jul", -1, @LEVMBRS("Years",0)) +
@PRIOR("Account_to_Sum"->"Aug", -1, @LEVMBRS("Years",0)) +
@PRIOR("Account_to_Sum"->"Sep", -1, @LEVMBRS("Years",0)) +
@PRIOR("Account_to_Sum"->"Oct", -1, @LEVMBRS("Years",0)) +
@PRIOR("Account_to_Sum"->"Nov", -1, @LEVMBRS("Years",0)) +
@PRIOR("Account_to_Sum"->"Dec", -1, @LEVMBRS("Years",0));
ELSEIF(@ISMBR("Mar"))
@SUMRANGE("Account_to_Sum", @CURRMBRRANGE("Months", LEV, 0, , 0)) +
@PRIOR("Account_to_Sum"->"Apr", -1, @LEVMBRS("Years",0)) +
@PRIOR("Account_to_Sum"->"May", -1, @LEVMBRS("Years",0)) +
@PRIOR("Account_to_Sum"->"Jun", -1, @LEVMBRS("Years",0)) +
@PRIOR("Account_to_Sum"->"Jul", -1, @LEVMBRS("Years",0)) +
@PRIOR("Account_to_Sum"->"Aug", -1, @LEVMBRS("Years",0)) +
@PRIOR("Account_to_Sum"->"Sep", -1, @LEVMBRS("Years",0)) +
@PRIOR("Account_to_Sum"->"Oct", -1, @LEVMBRS("Years",0)) +
@PRIOR("Account_to_Sum"->"Nov", -1, @LEVMBRS("Years",0)) +
@PRIOR("Account_to_Sum"->"Dec", -1, @LEVMBRS("Years",0));
etc, etc, etc . . .
ENDIF
When you get to December, it will just have the first line (with the @CURRMBRRANGE).
Hope this helps,
- Jake |