Current Date to EOM Filter
I'm trying to filter some data but what I want it to do is to filter based on the date you're going into the report and end of the month. Example if I go in today it would show me the pending arrangements for 8/9 to 8/31 and tomorrow would be 8/10 to 8/31, etc.
I'm thinking I have to do a beast mode for this, but let me know what would work the best.
I would use a combination of the date range filter and a beast mode, but you could also do it all with a beast mode if you wanted.
Set the date range filter to This Month
Create a beast mode that looks like this:
CASE WHEN 'arrangementdate' >= CURRENTDATE() THEN 'Include' ELSE 'Exclude' END
Drag the beast mode into your filters and filter to Include
If you wanted to do it all in beast mode, you can leave your date range filter to All Time and then use the following beast mode:
CASE WHEN 'arrangementdate' >= CURRENTDATE() AND MONTH('arragementdate') = MONTH(CURRENTDATE()) THEN 'Include' ELSE 'Exclude' END**Check out my Domo Tips & Tricks Videos
**Make sure to any users posts that helped you.
**Please mark as accepted the ones who solved your issue.2
To add my two cents I'd recommend going a bit further in the beast mode as MONTH will return the month number so if you're going multiple years in the future you'll get unexpected results. I'd recommend the LAST_DAY function instead which returns the last day of the month for a given date.
CASE WHEN `arrangementdate` BETWEEN CURRENTDATE() AND LAST_DAY(CURRENTDATE()) THEN 'Include' ELSE 'Exclude' END
You may also find this write up I've done in the past about BOM/EOM and BOW/EOW calculations:**Was this post helpful? Click Agree or Like below**
**Did this solve your problem? Accept it as a solution!**2
Thanks for the assistance @MarkSnodgrass and @GrantSmith, the below is what I used:
CASE WHEN `Date_Arrangment_Set_for` >= CURRENT_DATE() AND `Date_Arrangment_Set_for` <= LAST_DAY(CURRENT_DATE()) THEN 'Include' ELSE 'Exclude' END
Not sure why but when I was using BETWEEN based on Grant's suggestion it did not want to accept it in the beast mode and when I used Mark's it gave me a bit more in the amount. I'll figure out why later but for lack of time this is what worked for me and I validated the data and the amount matches. I think it's a hybrid of both of your suggestions. Thanks very much! 😊0
@Shumilex - the Calculated Field syntax does not like the BETWEEN clause. The formula that you provided should work for you. However, I would recommend being more descriptive with your filter expressions. I know that for the current use case, you are only interested in including future dates in the current month. But what happens when you want to see what has already happened this month?
I would write something like this:
CASE WHEN `Date_Arrangment_Set_for` < CURRENT_DATE() THEN 'Historic' WHEN `Date_Arrangment_Set_for` = CURRENT_DATE() THEN 'Today' WHEN `Date_Arrangment_Set_for` > CURRENT_DATE() THEN 'Future' END
You can now use this field as a filter. In your case, you would choose to include 'Today' and 'Future', or to exclude 'Historic'. I would then follow @MarkSnodgrass suggestion and include a date filter on the card for current month. This way, if someone wanted to see remaining events for the current quarter or year, they just need to change the date selection from month to quarter, etc.
You could even add a quick filter on the case statement so that if someone wanted to decide to look at historic vs future, they could do so by changing the quick filter.
This would just give your report more flexibility to allow more exploration, IMO
“There is a superhero in all of us, we just need the courage to put on the cape.” -Superman
- 7.7K All Categories
- 5 Connect
- 921 Connectors
- 244 Workbench
- 477 Transform
- 1.8K Magic ETL
- 60 SQL DataFlows
- 446 Datasets
- 40 Visualize
- 199 Beast Mode
- 2K Charting
- 8 Variables
- 20 Cards, Dashboards, Stories
- 1 Automate
- 348 APIs & Domo Developer
- 82 Apps
- 14 Predict
- 3 Jupyter Workspaces
- 11 R & Python Tiles
- 241 Distribute
- 59 Domo Everywhere
- 241 Scheduled Reports
- 15 Manage
- 36 Governance & Security
- 28 Product Ideas
- 1.1K Ideas Exchange
- Community Forums
- 14 Getting Started
- 1 Community Member Introductions
- 49 Community News
- 18 Event Recordings
- 579 日本支部