You're probably not still wrestling with this 5 months later, but I was recently faced with the same issue and found a solution, so thought I would post. You were really close. Try:
If DATEDIFF('month',max([Date]),total(max([Date]))) < 12 then 1 else 0 end
The total() part is a table calc, so you'll need to set it to "Compute using" the Date field you're filtering on. Then drop this on the filter shelf to show "1" and exclude "0".
Thank you so much Brian. Actually, I found a little different solution that involves a set that looks at the top 12 values in Date, but this version is a lot better. Thanks!!
Glad that worked for you Todd. Playing with this further, I've found it works great for a basic aggregate (e.g. revenue by month) chart, but the table calc doesn't work as intended once you start filtering the data or adding other dimensions. I found an alternative approach that just pulls a top 12 months with a filter on MY(Date), but I'm curious to see your approach with a set. Can you give any detail on what you did?
- Create a set.
- On the "Top" tab, click "by field", then select "top" 12 by [date field] and select "maximum"
- Call the set "Recent 12 months"
- Follow the same procedure above for a "Recent 24 months"
- Create a calculated field with the following formula:
IF [Recent 12 months] AND [Recent 24 months] THEN "CR12" ELSEIF
NOT [Recent 12 months] AND [Recent 24 months] THEN "PR12" ELSE "> 24 months" END
- This will show you Current R12 AND Previous R12 AND ">24 months"
- Of course, you could go on and on with R12 periods.
That makes sense, Todd. Thanks for the detail.