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)