MTD, MTD Last Year Beastmodes do not work properly with Date filter

verytiredgirl

I created 2 Beastmodes that I can drag into the 'Values' in the Pivot Table card:
MTD = SUM(CASE
WHEN Month = MONTH(CURRENT_DATE()) AND Year = YEAR(CURRENT_DATE()) THEN IFNULL(Amount * -1, 0)
ELSE 0
END)

MTD Last Year = SUM(CASE
WHEN Month = MONTH(CURRENT_DATE()) AND Year = YEAR(CURRENT_DATE()) - 1 THEN IFNULL(Amount * -1, 0)
ELSE 0
END)

I don't have October data, so the MTD is 0.00

Then I created a year & a month dropdown selector (with the same dataflow) and randomly picked a month, year. But MTD and MTD Last Year show all 0.00

But if specify the exact month and year in my Beastmode, the pivot card does show the correct number (however, with this, I cannot dynamically filter month, year)

ggenovese

There are a couple things going on

1. I'd recommend that Month and Year be variables, it sounds like they are filter cards built off the same dataset as your MTD/Last Year MTD card

2. Assuming that they are filter cards, then when you choose a Month and Year this filters your data to a single month and year which would explain why the Last Year MTD beast mode isn't returning the correct results

3. Your MTD beast mode will only calculate a non-zero value for the Current Month and Year

4. If Month and Year are already variables, that's great then you just need to add another beast mode that you will place in your filters:

CASE 
WHEN <month variable name> = month(<your date field>) 
AND (<year variable name> = year(<your date field>) OR year(<your date field>) = (<year variable name>-1)) 
THEN 'Include' 
ELSE 'Exclude' 
END

then filter the card on 'Include', this will give you the selected month and year, and also the same month for the prior year

Finally, you have to update your MTD and MTD Last Year beast modes:

— MTD
SUM(CASE

WHEN <month variable name> = MONTH(<your date field>) AND <year variable name> = YEAR(<your date field>) 
THEN Amount * -1


END)




— MTD Last Year 

SUM(CASE

WHEN <month variable name> = MONTH(<your date field>) AND <year variable name>-1 = YEAR(<your date field>) 
THEN Amount * -1


END)

verytiredgirl

Thank you so much, I'll try this. I haven't created any variables before so quick questions on creating them.

So right now I only have data from Feb 2023 - Sep 2024, but I'll update this dataset in the future. When picking a year it should start from 2024 onwards, do I need to add 2025, 2026 in the variable right now, or I could add/change it in the future?

And for the Month variable, would you suggest to create a Month Number or Month Name? I would like to see 'Jan', 'Feb' in order when I'm picking the Month filter.

And for the default value, can you do YEAR(CURRENT_DATE()) or MONTH(CURRENT_DATE())?

I'm planning on having a YTD and YTD Last Year columns as well, is it still applicable?

ggenovese

Variables are pretty awesome, especially for the sort of reporting you're doing where there are a fixed set of months that will never have to change and a limited number of years that need to be updated infrequently.

Regarding your questions:

1. You can update the years at any time
2. What I sent earlier assumed a month number, but I can see how a name works better for the variable. I'd suggest populating it with month names and then update the beast modes with

<month variable name> = MONTHNAME(<your date field>)

3. the Default Value can't be an expression unfortunately, but you could add a month to your variable called 'Current Month' and then have your beast mode handle it that way

4. YTD/YTD Last Year would require and update to the filtering beast mode, so that it returns full years rather that specific month and years. One way to handle it would be to update the beast mode if you think your users are going to be selecting years often. But another way to handle it would be to just save your card with Last Two Years selected as the date range, going this route would eliminate the need for the Year variable.

verytiredgirl

Thank you for your answer, it was very helpful. However, I don't quite understand this BeastMode:

1. CASE
2. WHEN <month variable name> = month(<your date field>)
3. AND (<year variable name> = year(<your date field>) OR year(<your date field>) = (<year variable name>-1))
4. THEN 'Include'
5. ELSE 'Exclude'
6. END

Isn't it doing the same thing with the MTD/MTD Last Year Beastmode already with the Case When?

ggenovese

You're right, in hindsight it does seem redundant! I guess I was thinking by minimizing the rows by filtering would ensure that you'd get the correct results. However, now especially because you want to add YTD on the same card it seems like you can handle it all without any special filtering.