Beast mode for rolling count of purchasing customers within a specified period month over month
I'm trying to develop a beast mode formula to help me derive count of customers who have purchased within the last 30, 60, and 90 days, which rolls month over month. For example, the count of patients who have purchased within 90 days prior to September, within 90 days prior to October, and within 90 days prior to November. Whether in a chart or table, these dynamic values would be reported for the respective month. Based on what I've found so far, it seems this might be achieved in beast mode with a formula like this, which would be used as a filter (set to 'Yes"), with the 'Order Completed Date' on the x axis (summarized by "month') and the customer count (count(distinct 'Customer ID') on the Y-axis:
case when date(`Order Completed Date`)<date('DateCol') AND date(`Order Completed Date`)>date('DateCol')-30 then 'Yes' else 'No' end
The formula validates, but when I add to the chart filter, it is blank with no options (no 'yes' or 'no' values to select). I also tried using 'DateColumn' instead of 'DateCol', which appears in some of the formulas in the beastmode interface (e.g. Date_Sub('DateCol', interval 30 day)), but it does not seem to make a difference.vIf I could get it to work, I'd simply replicate this for 30 and 60 days, but I can't seem to get a solution that works with 'DateCol'.
I welcome any feedback or suggestions.
FURTHER DETAIL, IF HELPFUL
"90 days (rolling 90 day look), it’s how many people are with us (defined by Purchased within the last 90 days) that started their relationship with us >90 days ago. Said another way, how many customers persist with us on a rolling 90 day retention look. Then the 30 day window will be a tighter view of that, then on a rolling 60 day look. I’m making the assumption that the customers who ordered >90 days ago and haven’t shopped again are lapsed or lost customers. Customers outside 60 and have not purchased w/in the last 60 days, but purchase between 60-90 days are at risk of lapsing"