Filter list based on previous calendar month

Question

We have a list and need to create a view based on previous calendar month.

I have created two new calculated Date and Tine columns:

• _StartPreviousMonth = DATE(YEAR(Created),MONTH(Created)-1,1)
• _EndPrevioousMonth = DATE(YEAR(Created),MONTH(Created),1)-1

These are working as expected.

An item created in November shows start of 11/01/2023 and end of 11/30/2023. We are now in December 2023, and I need the view to show only items in November. The view filters are _StartPreviousMonth is less than or equal to [TODAY] and _EndPrevioousMonth is greater than or equal to [TODAY]. This returns all items from December 2023. I need to be able to pull November’s items. Any suggestions?

Answer 1

An item created in November shows start of 11/01/2023 and end of 11/30/2023

No, with the formula you used, an item created in November will have 10/01/2023 and 10/31/2023. And this is correct regarding the name of the column (previous month, not current month!) and the formula (MONTH - 1).

My suggestion: create calculated columns with current month

_StartCurrentMonth = DATE(YEAR(Created),MONTH(Created),1)
_EndCurrentMonth = DATE(YEAR(Created),MONTH(Created)+1,1)-1

And then for the view, use a filter with [TODAY]-30 but in some case, it will not work (for example: in February/March).