Beast mode for rolling count of purchasing customers within a specified period month over month

High level there are two ways of approaching this.

1) for each period on your X axis (month), you want to force your dataset to include ALL THE DATA of interest (i.e. the last 90 days) for that period.  (so Rpt_Month Oct, would include Activity from Oct, Sept, Aug)

that way your beast mode is nice and simple

CASE WHEN date_diff(Rpt_Date, Activity_Date ) <= 90 then .... END

To do this, build a date dimension with one row per Rpt_month for all the Activity_Days in that Rpt_Month (each month would have 90 days.)

Alternatively, keep your data at the current granularity, and use the LAG() window function (submit a feature request to your CSM to have window functions enabled).  You'd do LAG(sum(Amount), 1) over (order by Rpt_Month desc) as demonstrated in this video.

https://www.youtube.com/watch?v=cnc6gMKZ9R8&t=25s

if you need COUNT DISTINCT, the LAG() method won't work b/c you can't get a collective distinct count across months, you'd get SUM(54 unique customers in Aug, 46 in Sept 47 in Oct) and it's likey you have overlapping customers between months.

The two different methods are

use LAG() in windowed functions (previously linked tutorial) OR this one https://www.youtube.com/watch?v=CDKNOmKClms

instead of current year / previous year, you'd build offsets for 90 day buckets.

-- Also, it would be ideal if this could be dynamic with variables.

if you watch the videos, neither option hardcodes dates.  so not a problem!

-- You mention "Rpt_Date", which I presume is placeholders for hard-coded value for the month in question.  

No.  In Solution2,  Rpt_Date would be the date column that you put on the axis.  As opposed to the activity_date which is the date the activity actually occured.  In your example " have purchased within 90 days prior to September, within 90 days prior to October, and within 90 days prior to November." so the report_date (I also referred to it as periods) 

-- you still have access to the GRAPH_BY settings in analyzer.  But if you want to change the granularity of the X-Axis, you'll probably find most success using LAG() functions (video 1) instead of the calendar_dimension (video 2)

DATE_SUB
Subtracts date or datetime values (as intervals) to date values in a date column.
DATE_SUB('DateCol', interval 12 day)

... this function won't work with your data in it's current state because if you put months on the axis, then your beast mode can only see data IN THAT MONTH.  (i.e. at most it can see the last 30 days of data, and never 90, because there are only 90 days in a month). 

Lol .... federated makes everything harder.

but that's fine.  build your date_dimension dataset in your database, and federate your JOIN'ed date + transaction table.  then you can do it all in simple beast modes.

you can't count distinct with the LAG() function method.  I explained why in my first response.

"if you need COUNT DISTINCT, the LAG() method won't work b/c you can't get a collective distinct count across months, you'd get SUM(54 unique customers in Aug, 46 in Sept 47 in Oct) and it's likey you have overlapping customers between months."