Month to yesterday - at first day of month

The principle shown in this article can also be used for scenarios like:

• Year to previous month - at first month of year
• Week to yesterday - at first day of week

Wrong method

First, let me show the wrong way of setting up 'Month to yesterday':

We must use a range (i.e. the 'Between operator), and to hit the first day of the month we add a Day offset and set it to 'Specific, 1':

For the other end of the range we want to hit yesterday, which can be done by a Day offset 'Relative, -1':

The above example is based on the date 20th January 2022 and indeed does give the correct result: From 1st January to 19th January.

However, if we do the same thing on the 1st of February, we will get this result:

The result here is obviously wrong: From 1st February to 31st January.

Instead, we would have expected this result: From 1st January to 31st January.

Correct Method

We need to modify the 'From' definition with another Day offset:

In this way, the 'From' will always move to yesterday before moving to the first day of the resulting month, and as seen in this example (based on the 1st February) the Day offset of -1 moves dates into January, and the next offset moves it to the 1st January.

This method will work consistently, also for mid month dates.

The same principle for 'Year to previous month':

The same principle for 'Week to yesterday':