Where are you applying this filter? The data sheet level? Its possible you can wrap that with a isnull expression
Check your formatting.
I assume the code you gave in your example above was just pseudo-code, and not the actual code, else that would give you an error.
Question: Is one field a date-time field and the other just a date field? If so, a date-time will never equal just a date (at least in my experience.)
You can get around that by wrapping the date-time field in the DATE() function:
IF DATE([date-time field]) <= [date field] ...
It would help if you uploaded a packaged workbook so I can see what you are doing. Otherwise, all I can do is guess at things. And that's not fun for me.
DATE([Effective Date]) <= DATE ("2016-06-31")
but it gives me null even though I know there are things with effective dates before June 31
There aren't 31 days in june! problem solved
Emily -- See attached.
Yes, I noticed the june 31 thing when I started playing with this.
I created a calc, hard coded to DATE("2016-06-31"). Displayed it, and I kept seeing July 1 instead. And I thought, maybe it's the format of the date you used inside the quotes. So I created a second calc using the format I prefer. Same July 1. Then I realized that June has only 30 days. But all the same, Tableau seems to be handling it gracefully.
I created a calc to use as a filter: [Order Date] <= [Calculation1] (and I changed the year to 2012 to accommodate the data used in Superstore.)
It works perfectly. Play with it to see.
I'll say this: The filter works because it is doing less-than-or-equal. If you are not grabbing a range but instead a specific date, THEN the June 31 issue could bite you.
I created this in 8.2 because I don't know what version you are on, but it will upgrade to yours, of course.
And as for proprietary data, you can still upload sample workbooks to demonstrate what you are encountering, as I did here. It doesn't have to be your company's data or actual workbook.
In fact, I made a demo video to show a way to anonymize your stuff if you can only seem to be able to duplicate it on your own stuff. It is linked here:
superstore -- date example.twbx 985.7 KB