I love relative date filters in reports, but...
...I have seen them return unexpected results several times, compared to using a calendar date filter that seems like it should be the same date range.
For example, I have a report that I just ran today (10/9/2020) of Contacts and Accounts with a cross filter on Opportunities, and a date filter on the Close Date of the cross filter, that returns a record count of:
223 records when then filter is "greater than LAST 3 YEARS"
444 records when the filter is "equal to LAST 3 YEARS"
489 records when the filter is "greater than 1/1/2018"
516 records when the filter is "greater than 10/1/2017"
I don't understand what it means to use a relative date filter with any operator other than "equal to"; and I thought that "LAST 3 YEARS" meant "any date that has a year of the current year (2020 in my case), the previous year (2019), and the year before that (2018) - but clearly I am wrong.
Can anyone point me to a resource that explains how to translate each different relative date filter operand and operator into the equivalent calendar date filter, so I can explain to my staff? Right now I have to tell them to use calendar date filters because relative date filters appear "unreliable". I know they work, I just don't know precisely how.
I have this page bookmarked, and I check it all the time. Relative Date Filter Reference.
n YEARS AGO: Starts at 12:00:00 AM on January 1 of the calendar year n years before the current calendar year and continues through the end of December 31 of that year.
LAST n YEARS: Starts at 12:00:00 am on January 1, n+1 years ago. The range ends on December 31 of the year before the current year.