1 of 1 people found this helpful
James try using your date field on the filter shelf twice. Set one to exclude the current month. Set the other to relative date. This won't change the relative date dialog box, but if the use click month to date the sheet will blank.
Thanks Shawn - if I understand correctly you mean to manually exclude whatever month is current? Or is there a check box somewhere that says "exclude current month"?
Ideally I would like to automate completely, but if I have to have the current date excluded each month manually that isn't a deal breaker, so I can definitely use your suggestion.
Shawn - how would you automatically exclude the current month in your solution? I don't see any way to make a relative date filter an exclusion filter.
Here's a formula that will work every month without needing to manually set the filter each month. (I work with irregularly arriving data, so relative date filters often get in my way and I tend to use calculated fields instead.) This formula returns true for the prior four months only and can be used on the Filters Shelf instead of a relative date filter:
[Date] >= DATEADD('month',-4,DATETRUNC('month',TODAY())) AND [Date] < DATETRUNC('month',TODAY())
Thanks a lot Jonathan, this works a charm! I will be using this formula a great deal in the coming weeks!
Shawn, if you do know of a way to add that relative exclusion, please let us know - if there is no way I certainly hope that is a functionality they will add soon. It would be very popular/useful.
I missed the "automatically".
No worries! - That is still a good to know method, and simple for quick analysis.