Problem Statement: It is important to be able to compare one month or year to another and ensure that they are equal. This requires us to either hide the current month from a card since it isn’t complete, or show the activity in each month through the same point in time. For example, if it is the 15th of the month, just show the first 15 days of activity in each month. We didn’t want to manually adjust cards each month or have to explain why a month looks so different from another month.
Solution Statement: I was able to solve this problem by creating two different beast modes for the two different situations. If I want to exclude the current month from a card, I create a beast mode that looks at the date in the dataset and checks to see if it is less than or equal to the last day of the current date’s previous month. If it is, then I assign it a value of “include”, otherwise I assign it a value of “exclude”. I put this beast mode in my filter and filter to include. Here is the beast mode:
Beast Mode for including completed months
CASE WHEN `EntryDate` <= LAST_DAY(DATE_SUB(CURRENT_DATE(), interval 1 MONTH))
THEN ‘Include’
ELSE ‘Exclude’
END
If I want to only show activity through the same point in time each month, I utilize the DAYOFMONTH function to evaluate whether the day is less than or equal to today’s day. I then include or exclude and drag it into the filter just like my other beast mode. This allows us to see how the current month is trending compared to the same point in time as previous months. Here is the beast mode:
Beast Mode for including only up to the same point in time of each month
CASE WHEN DAYOFMONTH(`EntryDate`) <= DAYOFMONTH(CURRENT_DATE())
THEN ‘Include’
ELSE ‘Exclude’
END
Demo: I created a video that demonstrates how to use each function: