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

kcalhoun

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?

ColemenWilson

You could use a filter on your card like this:

CASE
WHEN DAYOFMONTH(CURRENT_DATE()) = 1 AND DateField < CURRENT_DATE() AND DateField >= CURRENT_DATE() - INTERVAL 1 MONTH THEN 'In'
WHEN DAYOFMONTH(CURRENT_DATE()) != 1 AND DateField >= LAST_DAY(CURRENT_DATE()) + INTERVAL 1 DAY - INTERVAL 1 MONTH AND DateField <= LAST_DAY(CURRENT_DATE()) THEN 'In'
ELSE 'Out' END

I 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

ColemenWilson

You could use a filter on your card like this:

CASE
WHEN DAYOFMONTH(CURRENT_DATE()) = 1 AND DateField < CURRENT_DATE() AND DateField >= CURRENT_DATE() - INTERVAL 1 MONTH THEN 'In'
WHEN DAYOFMONTH(CURRENT_DATE()) != 1 AND DateField >= LAST_DAY(CURRENT_DATE()) + INTERVAL 1 DAY - INTERVAL 1 MONTH AND DateField <= LAST_DAY(CURRENT_DATE()) THEN 'In'
ELSE 'Out' END

I 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

kcalhoun

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.

ColemenWilson

Oh it should be under my answer. If not no worries, @Grant.Stowell will get it marked! Thanks @kcalhoun!