Issue with Month to Date (MTD) Calculation on the Last Day of the Month

Hey Community,
I have a head-scratcher. We are experiencing an issue with our Month to Date (MTD) calculation in our Daily Snapshot reports. Throughout the month, the MTD calculation works as expected, showing the cumulative revenue for the current month. However, on the last day of the month, the calculation does not roll up the revenue for the entire month. Instead, it displays the revenue earned only on the current day the report is being generated.
On the first day of the month, it should still show the Month to Date for the previous month (since the report is for the last day of sales, 7/31, not 8/1).
To summarize:
- The report is generated at 8 AM each day.
- The current MTD calculation is set to show the revenue for the current month up to the current day.
- On the last day of the month (7/31), the user will expect the MTD field to show the total revenue for the previous month (7/1 - 7/31 for example), not the current month in progress.
Is there a particular technique to accomplish this?
Best Answer
-
You could use a filter on your card like this:
CASE
WHEN DAYOFMONTH(CURRENT_DATE()) = 1 ANDDateField
< CURRENT_DATE() ANDDateField
>= CURRENT_DATE() - INTERVAL 1 MONTH THEN 'In'
WHEN DAYOFMONTH(CURRENT_DATE()) != 1 ANDDateField
>= LAST_DAY(CURRENT_DATE()) + INTERVAL 1 DAY - INTERVAL 1 MONTH ANDDateField
<= LAST_DAY(CURRENT_DATE()) THEN 'In'
ELSE 'Out' ENDI tested it and it seems to work. It's not pretty but it gets the job done. And yes I know I need to start using the DATE_SUB() function and someday I will haha
If I solved your problem, please select "yes" above
1
Answers
-
You could use a filter on your card like this:
CASE
WHEN DAYOFMONTH(CURRENT_DATE()) = 1 ANDDateField
< CURRENT_DATE() ANDDateField
>= CURRENT_DATE() - INTERVAL 1 MONTH THEN 'In'
WHEN DAYOFMONTH(CURRENT_DATE()) != 1 ANDDateField
>= LAST_DAY(CURRENT_DATE()) + INTERVAL 1 DAY - INTERVAL 1 MONTH ANDDateField
<= LAST_DAY(CURRENT_DATE()) THEN 'In'
ELSE 'Out' ENDI tested it and it seems to work. It's not pretty but it gets the job done. And yes I know I need to start using the DATE_SUB() function and someday I will haha
If I solved your problem, please select "yes" above
1 -
Hey Colemen, this worked great for that report! Thank you so much!
I don't see where I'm supposed to select "yes" from, the circled three dots next to your name produce an empty dropdown.0 -
Oh it should be under my answer. If not no worries, @Grant.Stowell will get it marked! Thanks @kcalhoun!
If I solved your problem, please select "yes" above
1
Categories
- All Categories
- 2K Product Ideas
- 2K Ideas Exchange
- 1.6K Connect
- 1.3K Connectors
- 311 Workbench
- 6 Cloud Amplifier
- 9 Federated
- 3.8K Transform
- 657 Datasets
- 115 SQL DataFlows
- 2.2K Magic ETL
- 815 Beast Mode
- 3.3K Visualize
- 2.5K Charting
- 81 App Studio
- 45 Variables
- 775 Automate
- 190 Apps
- 481 APIs & Domo Developer
- 81 Workflows
- 23 Code Engine
- 40 AI and Machine Learning
- 20 AI Chat
- 1 AI Playground
- 1 AI Projects and Models
- 18 Jupyter Workspaces
- 410 Distribute
- 120 Domo Everywhere
- 280 Scheduled Reports
- 10 Software Integrations
- 144 Manage
- 140 Governance & Security
- 8 Domo Community Gallery
- 48 Product Releases
- 12 Domo University
- 5.4K Community Forums
- 41 Getting Started
- 31 Community Member Introductions
- 114 Community Announcements
- 4.8K Archive