Skip navigation

Honor date granularity on continuous "Range of Dates" filters

score 20
You have not voted. Active

Date filters that are configured as a continuous "Range of Dates" slider do not honor the specified granularity. For instance:


For example, if Superstore [Order Date] is configured as a MONTH (Continuous) filter and shown as a dual slider, it shows like this:


Even though the slider displays month/year only (thereby giving the impression that it covers the entirety of those months), the slider itself, and the underlying filter, are still tracking day-level granularity.


This can be seen if the slider is moved slightly:


All of July disappeared. By looking at the Edit Filter dialog, we can see why:


Since [Order Date] is configured as MONTH (Continuous), it is processed as DATETRUNC('month', [Order Date]) before being compared to the filter. But this means that all July 2016 dates are DATETRUNC'd to 7/1/2016. Since the dual slider still tracks day-level granularity, as soon as it moved a little bit (to 7/13/2016 in this example), all of the July data got filtered out.


This is immensely confusing to end users, as well as designers who have not yet come to realize this caveat. Designers must eschew the Range-of-Dates slider interface completely, if they need their filter to behave as anything other than Exact Day.


If a continuous Date filter is configured to a level of granularity other than Exact Day, the dual slider interface should honor the level of granularity selected.



Community Forum questions related to this issue (may not be comprehensive):

Continuous date slider filter doesn't include first date

Unexpected MONTH(Date) Filtering

Date range filter - misses the first value

Date Ranges as a Month/Year


(NOTE: Each of the threads above includes excellent explanatory comments from Jonathan Drummey. It has obviously been a known issue for a while. Nevertheless, I could not find an Idea posted to request a change to the behavior. If an Idea has already been posted, I apologize for the duplicate Idea. My search-fu must not be strong.)


Vote history