Bug? Selecting in the Next or in the Previous excludes Today
I have a scenario where I need to dynamically select a range of dates one month previous and six months forward. I've set up my Filtered rows to have a condition of In the Next 6 Months or In the Previous 1 Month. I got strange results. So I took it to the day level and what I found is that today's data is always excluded.
I'd expect these conditions to do a greater than or equal to today to the limit or less than or equal to today up to the limit. It appears to be doing a greater than/less than only.
This behavior is currently by design. The "Is In Next/Previous" filters do not include current. You can achieve the "Is in Next or Current" behavior by using an advanced filter and combining both types of filters, as shown in the screenshot below.
The challenge is that I'm usually trying to get a range of X next months or Y Previous Months. For example, give the previous three months and the next 6 months of budget data for a quarterly review.
While technically this is three sets of data(Current month, previous three months, next six months), this is approaching the issue in a mathematical way of thinking. The end user will be thinking of this situation as defining a range of dates, rather than distinct sets. I believe the range approach is what is used by Excel. As Excel sets the user perception, you may want to reconsider this behavior. It was an end user who brought this behavior to my attention. The discontinuity of approach with Excel will cause user confusion.
In order for your solution to work, I have to create separate datasets and merge. This seems inefficient and certainly not obvious.
Couldn't the issue be solved by adding a robust date dimension? I'm guessing you already have one, but on a high level what your describing is just different ways to segment dates. I would imagine the above example would work in some filtering. But on another note, why wouldn't you just expose the year and month to the end user and allow them to slice the data however they want? They can include or exclude every combination they want in real time without you having to preset anything for them...
I'm reporting over Project Online data, which has a large number of records, leading to throttling issues with OData in Office 365 if you don't prefilter.
Dates, man do we have a plethora of dates as well. An assignment time-phased data set can easily have over a million records or more.
Best practice is to create a rolling time window subset, within which the user has the ability to filter. Hence, when the user found out the current month was excluded from their dataset, they were concerned.