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.