Evaluating Month and 12 months Information utilizing Beast Modes

Drawback Assertion: It is very important have the ability to evaluate one month or yr to a different and make sure that they’re equal. This requires us to both cover the present month from a card because it isn’t full, or present the exercise in every month by means of the identical time limit. For instance, if it’s the 15^th of the month, simply present the primary 15 days of exercise in every month. We didn’t wish to manually regulate playing cards every month or have to clarify why a month appears so totally different from one other month.

Resolution Assertion: I used to be in a position to clear up this drawback by creating two totally different beast modes for the 2 totally different conditions. If I wish to exclude the present month from a card, I create a beast mode that appears on the date within the dataset and checks to see whether it is lower than or equal to the final day of the present date’s earlier month. Whether it is, then I assign it a price of “embody”, in any other case I assign it a price of “exclude”. I put this beast mode in my filter and filter to incorporate. Right here is the beast mode:

Beast Mode for together with accomplished months

CASE WHEN `EntryDate` <= LAST_DAY(DATE_SUB(CURRENT_DATE(), interval 1 MONTH)) 

THEN ‘Embrace’

ELSE ‘Exclude’

END

If I wish to solely present exercise by means of the identical time limit every month, I make the most of the DAYOFMONTH perform to judge whether or not the day is lower than or equal to immediately’s day. I then embody or exclude and drag it into the filter identical to my different beast mode. This permits us to see how the present month is trending in comparison with the identical time limit as earlier months. Right here is the beast mode:

Beast Mode for together with solely as much as the identical time limit of every month

CASE WHEN DAYOFMONTH(`EntryDate`) <= DAYOFMONTH(CURRENT_DATE()) 

THEN ‘Embrace’ 

ELSE ‘Exclude’ 

END

Demo: I created a video that demonstrates the right way to use every perform: