Skip to content

    • Cards, Dashboards, Stories

    Cards, Dashboards, Stories

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

    Member
    in Cards, Dashboards, Stories

    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?

    Welcome!

    It looks like you're new here. Members get access to exclusive content, events, rewards, and more. Sign in or register to get started.
    Sign In

    Best Answer

    • Coach
      Answer ✓

      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

      If I solved your problem, please select "yes" above

    Answers

    • Coach
      Answer ✓

      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

      If I solved your problem, please select "yes" above

    • Member

      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.

    • Coach

      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

    Welcome!

    It looks like you're new here. Members get access to exclusive content, events, rewards, and more. Sign in or register to get started.
    Sign In

    Welcome!

    It looks like you're new here. Members get access to exclusive content, events, rewards, and more. Sign in or register to get started.
    Sign In

    Quick Links

    Categories