I am trying to create various period over period beastmodes, some for summary numbers and some for the charts themselves. I have variations using currentDate() in the formula - that works on the default view card, but if you add a drill-down or filter by a historical date range they break.
Here is a FYTD/FYTD change ($) example. In this case the default view is FYTD, and this works; however, if a user changes the end date via filter, the formula breaks because it still calculates the prior period based on an interval from the current date. I tried changing to Max(`Date`) but that breaks the formula.
sum(CASE WHEN (CASE WHEN MONTH(`Date`) > 4 THEN YEAR(`Date`)+1 ELSE YEAR(`Date`) END) = (CASE WHEN MONTH(CURRENT_DATE()) > 4 THEN YEAR(CURRENT_DATE())+1 ELSE YEAR(CURRENT_DATE()) END) THEN `Gross Sell-In Revenue Shipped LC` ELSE 0 END)
-
sum(CASE WHEN `Date` <= DATE_SUB(CURRENT_DATE(),INTERVAL 1 YEAR) AND (CASE WHEN MONTH(`Date`) > 4 THEN YEAR(`Date`)+1 ELSE YEAR(`Date`) END) = (CASE WHEN MONTH(CURRENT_DATE()) > 4 THEN YEAR(CURRENT_DATE())+1 ELSE YEAR(CURRENT_DATE()) END -1) THEN `Gross Sell-In Revenue Shipped LC` ELSE 0 END)
AND here is a Last 7 days over prior 7 days beastmode that is intended for a drill down card after you drill into a specific month in the default view. If you drill into the most recent month it works, but for historical months it does not.
LEFT(100*
((sum(CASE WHEN DATEDIFF(CURRENT_DATE(),date(`Date`)) < 8 THEN `Revenue` ELSE 0 end) /
sum(CASE WHEN DATEDIFF(CURRENT_DATE(),date(`Date`)) < 8 THEN `Cost+Fee` ELSE 0 end))
-
(sum(CASE WHEN DATEDIFF(CURRENT_DATE(),date(`Date`)) >= 8 AND DATEDIFF(CURRENT_DATE(),date(`Date`)) <= 14 THEN `Revenue` ELSE 0 end)) /
sum(CASE WHEN DATEDIFF(CURRENT_DATE(),date(`Date`)) >= 8 AND DATEDIFF(CURRENT_DATE(),date(`Date`)) <= 14 THEN `Cost+Fee` ELSE 0 end))
/
((sum(CASE WHEN DATEDIFF(CURRENT_DATE(),date(`Date`)) >= 8 AND DATEDIFF(CURRENT_DATE(),date(`Date`)) <= 14 THEN `Revenue` ELSE 0 end)) /
sum(CASE WHEN DATEDIFF(CURRENT_DATE(),date(`Date`)) >= 8 AND DATEDIFF(CURRENT_DATE(),date(`Date`)) <= 14 THEN `Cost+Fee` ELSE 0 end))
,5),
Can anyone provide any help? Thanks in advance!