Hi everyone!

I am trying to work out an issue...our church has a week of Monday through Sunday.

I am trying to do averages on our months (which is dicated by Sundays)

For example, the first Sunday in February is 2/4, so I want Monday - Sunday to be a month of February (since the days leading up to the Sunday all fall into the Sunday in February...
Here is my code, but unfortunatley, 1/28/24 is falling into February as well.

SELECT
         *,
         
            CASE
                 WHEN date  = DATEADD(day, 6 -DATEPART(weekday, date), date) THEN DATEPART(month, date) /* Journey Month is the current month for Sundays*/
                 ELSE DATEPART(month, DATEADD(day, 6 -DATEPART(weekday, date), date))
             END AS JourneyMonth
FROM  "FINAL Check In Events and Headcounts (with Ministry|Campus|Holiday)"

--------------------------------------------------------------------------------

Can anyone help me solve this?
Thank you!!!