Is there a Beastmode I can configure for Rolling 30 period?

MichaelClark
MichaelClark Wichita Falls, Texas ⚪️

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.

Thanks

Michael

Best Answer

  • MarkSnodgrass
    MarkSnodgrass Portland, Oregon 🟤
    Accepted Answer

    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'
    END
    


    You could also use DATE_SUB with the current date to go back one month

Answers

  • MarkSnodgrass
    MarkSnodgrass Portland, Oregon 🟤

    I would look at this post by @GrantSmith who lays out this functionality nicely.


  • MichaelClark
    MichaelClark Wichita Falls, Texas ⚪️

    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.

    Thanks!

    Michael