AnsweredAssumed Answered

Calculating Rolling 12 months

Question asked by sccardais on Feb 6, 2016
Latest reply on Feb 7, 2016 by @ppear

I'm having trouble calculating a value in Table A (MONTHS) that summarizes data in Table B (SURVEYS) if the SurveyDate in SURVEYS falls between 2 dates in MONTHS and if I have at least 12 months of prior data.


  • The purpose is to show trends and remove seasonality by showing data in groups of 12 month periods - with each month summarizing the current month and the previous 11 months - sometimes called a Rolling 12 months.


SURVEYS contains about 3,500 individual customer surveys with scores (1-10) Each survey has a SurveyDate converted to show all dates from the 1st day of the month. (e.g. 5/15/2014 is converted to 5/1/2014)  For each month in which I have 11 months of previous data, I want to show the average score from SURVEYS and perform other calcs once I get the relationship working.


To do this, I created a new table MONTHS as shown below.


So far, I haven't been able to create a relationship that does what I want.


How can I relate MONTHS to SURVEYS to match records in SURVEYS with a Survey Date between MONTHS:: c_RTM_Start and MONTHS::RTM_End  IF there are at least 12 months of data in SURVEYS prior to MONTHS::RTM_End.