White Belt

## How to Make a Period Over Period Beastmode Work with Date Filtering

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!

Brown Belt

## Re: How to Make a Period Over Period Beastmode Work with Date Filtering

I have seen the same problem and could not make it work in beast mode.  I think the only way it would work is if we could pass the current date range selected on the card to the beast mode as a parameter.

I have created dataflows that join the dataset back to itself and create columns for the current year and previous year. This allows for easy variance calculations and charting of variances as well.

Hopefully someone else can provide more insight of how to accomplish in a beast mode.

Chris

-----------------
Chris
Major Brown Belt

## Re: How to Make a Period Over Period Beastmode Work with Date Filtering

I believe that Chris is correct. It appears that all date filters need to be built into the BeastMode itself and they will not be dynamic. They have some helpful BeastMode examples for different calculations in their Knowledge Base article here:

I know that they built the Period over Period chart types so that they do the dynamic calculations for you. But, they are limited to only a single metric. This is their article on those charts: