I have a google sheet of sales deals over time, deals are in various deal stages such as closed won, closed lost, closed terminated etc. Main date dimension is close date and metrics would be monthly recurring revenue and annual recurring revenue. I want to be able to display the cumulative closed won ARR of deals but in the chart just show the data for the last 12 months. That is to say that the data in the previous month should be the cumulative of all closed won deals not just closed won in the last 12 months which is essentially the issue I have been running into, i.e. looking for a visual filter rather than a data filter such as date range gives me. In the end I would like simply a chart of cumulative sales showing the last 12 months data, another chart of cumulative sales broken out by say industry type (another dimension I have), the latter maybe in a stacked chart?

I have tried creating a separate metric and dimension to see if that would in some way filter on the previous 12 months but that does not work. Dimension I called rolling 12 month filter IF(DATE_DIFF(CURRENT_DATE(), Close Date) <= 365, "Last 12 Months", "Older")

Then I created a metric called rolling 12 months

SUM(CASE
WHEN Rolling 12 month filter = "Last 12 Months" then ARR $ USD
ELSE 0
END)

I think this is essentially leading to the same problem that a date range filter leads to in that I am only summing those deals in the last 12 months rather than trying to sum up all deals