Dynamically show Dates

We have a collection of cards that all look like the image below, each for a different KPI (Demand, Traffic, etc). These cards are built utilzing beast modes that pull in MTD data based on a fiscal month. As a result the date ranges for each bar change daily. I'd like to add a card to the collection that dynamically populates the dates being pulled. For example, today the card would read something like "2016: 1/31/16-2/9/16 & 2017: 1/29/17-2/7/17". Then tomorrow the card would need to say "2016: 1/31/16-2/10/16 & 2017: 1/29/17-2/8/17." How can I make this possible?

MTD Image.jpg

Find more posts tagged with

Comments

dougstewc

Hi @juliannarenaud1. I want to make sure I understand your question correctly. Are you looking to have a new card that is only there to show the range for each year? If that is the case, would you be comfortable showing what the MTD beast modes look like? Thanks!

juliannarenaud1

Sure, here is the formula for MTD 2016 Demand:

SUM(
CASE WHEN DAY(`Date`) <= (DAY(SUBDATE(NOW(), INTERVAL 5 HOUR))+1)
AND YEAR(`Date`)+`Days to Add to Put Into Proper Fiscal YEAR` = (YEAR(SUBDATE(NOW(), INTERVAL 5 HOUR))-1)+`Days to Add to Put Into Proper Fiscal YEAR`
AND MONTH((`Date`)+`Days to Add to Put Into Proper Fiscal MONTH`) = MONTH((SUBDATE(NOW(), INTERVAL 5 HOUR))+ `Days to Add to Put Into Proper Fiscal MONTH`)
THEN `Transaction Revenue`
END)

Here is the formula for MTD 2017 Demand:

SUM(
CASE WHEN YEAR(`Date`)+`Days to Add to Put Into Proper Fiscal YEAR` = (YEAR(SUBDATE(NOW(), INTERVAL 5 HOUR)))+`Days to Add to Put Into Proper Fiscal YEAR`
AND MONTH((`Date`)+`Days to Add to Put Into Proper Fiscal MONTH`) = MONTH((SUBDATE(NOW(), INTERVAL 5 HOUR))+ `Days to Add to Put Into Proper Fiscal MONTH`)
THEN `Transaction Revenue`
END)

dougstewc

Thanks @juliannarenaud1. Have you tried creating a beast mode to identify the start and end dates and concat them together into a range and then show that in a table card?

juliannarenaud1

No, thoughts on what that might look like?

dougstewc

Do you have a dataset that defines the fiscal calendar within your instance? Something that shows each day, and what fiscal month that day is in?

juliannarenaud1

Yes, it's even already connected to the GA data used to populate the cards.