The caveat is the 30 day period I want to configure is Previous Month Second Tuesday to Current Month Second Tuesday.
Any assistance is appreciated in advance.
To determine the 2nd Tuesday of the month, you could use the DAYNAME function and the DAY function. The 2nd Tuesday of the month can only occur between the 8th and the 4th of the month, so you could write a beast mode like this:
CASE WHEN DAY('date') >= 8 AND DAY('date') <=14 AND DAYNAME('date') = 'Tuesday' THEN '2nd Tuesday'
ELSE 'Some other day'
You could also use DATE_SUB with the current date to go back one month
I would look at this post by @GrantSmith who lays out this functionality nicely.
This is great information in regards to calculating rolling average however, what I am attempting to is create a time period/date function for Microsoft Patch Tuesday's that looks at "last_sync" date field with "Review Period is between Previous Month Second Tuesday to Current Month Second Tuesday = True".
One idea I had was to create a Beast Mode with CASE statement to set the current year (and subsequent 4-5 years) month's second Tuesday's something like this:
CASE when 'last_sync' = '05-11-2021' then 'May 2021 Patch Tuesday' else
when 'last_sync' = '06-08-2021' then 'June 2021 Patch Tuesday' else
and continuing through the year(s).
The difficulty is the review period is not Last 30 days, which would be easy enough, but second Tuesday to second Tuesday.