I have a line chart with a rolling 12 month turnover calculation by month. Eventually I want to display the 12 most recent months of data, however my data set begins in 1/2012 so for now I need to display all months from 12/2012 and on (the months for which I have a valid 12 month rolling calc). Ideally I would set this up now to say the 12 most recent months that are >= 12/2012 and avoid updating the logic later.
If I filter out everything prior to 12/2012 with a filter on Report Effective Date then the calculation, which uses WINDOWS_SUM and WINDOWS_AVG, is impacted. The values for the displayed months need to remain the same when the prior months are excluded. I attempted to 'Hide' the dates instead but this didn't seem to work in the line chart.
I've attached an example with sample data. Any assistance would be much appreciated!
I was able to resolve this. I applied the following filter:
last()>=0 and last()<=11 and first()<-10