Date Filters on the First

I’m sure everyone has some common date filters that are used in their projects, and more than likely you’ve got a Month to Date (or Month to Yesterday depending on your ETL strategy) filter.  That filter is usually defined as “1st of the Month through Yesterday”, but the filter breaks if today is the 1st of the month, doesn’t it?  Given if today was November 1st, then the filter would resolve to “Nov 1st -> Oct 31st” which wouldn’t return any data.  There are a few ways to fix this I’m sure, but here’s the method that I use.

Certainly, this method will work for any kind of date sensitive filter, such as Week to Date or Year to Date, or anything, but with this example I’ll stick with “Month to Yesterday”.

In addition to the definition that I outlined above, the trick is to add an OR statement that will look back to the previous month, but only if today is the 1st of the Month (or last week if it’s the start of the week, whichever filter you’re working on).

Finally, add the second part of the condition using an ApplyComparison (which is a database pass through function for Filters) to check if today is the first.  In my environment, the database command is current_date but in MSSQL it would be getdate(), and so on.  In this way, only one side of this condition will ever be true.  Either today is not the first and the first part of the filter is valid, or today is the first, the first part of the filter doesn’t return any data, and the second part of the filter returns last month.

@brunocostalopes points out a much easier method, by just defining the filter as:

Day (ID) between "1st of the Month of Today -1" and "Today -1"

That works much nicer than my listed approach 🙂

The concept of “1st of the Month” was first added in 9.0, and this concept is one I’ve always used from the pre-9.0 days, and this simple approach never occurred to me.  Thanks Bruno, I will update my own filters too!

BONUS – Prior Year
Since I’m on the date filter subject, and this particular trick probably isn’t post-worthy on it’s own, here’s a simple method for building a Prior Year filter to compare today vs the same day last year, with consideration given to the day of the week.  This can be important in most business where comparing a Monday vs Sunday wouldn’t be valid, so you want the equivalent Monday from the Prior Year compared to the Monday of the Current Year.  Sound complicated?  It’s actually as easy as defining a filter to be:

Date(ID) - 364 days

Not only does it work to match Day of Week to Day of Week, but it also supports Leap Year!  This works because 364 is divisible by 7, which of course is the number of days in a week.  Give it a try, it’s a pretty simple solution to a problem that I’ve seen get over complicated in a hurry.

You may also like...