I am struggling to figure out time intelligence calculations in Domo. I feel what I am trying to do is fairly simple which is calculate the YoY change and graph by a categorical field.
For example, the chart below is an example of what I want my finished end product to look like. I know Domo has period over period cards but the only options are a single value KPI card or a chart with Time on the X axis but neither will solve my use case because I want YoY summarized by product.
DATA STRUCTURE
I am using a transactions table so there are a lot of important columns but for the sake of this example I will outline the 3 main columns
Solutions Explored
The closest I have gotten is adding columns to my dataset as follows.
- DATE_YoY = DATE(YEAR(TODAY()),MONTH(DATE),DAY(DATE))
- SALES_CY = CASE WHEN DATE = DATE_YOY THEN SALES ELSE 0 END
- SALES_PY = CASE WHEN YEAR(DATE) - YEAR(DATE_YoY) = -1 THEN SALES ELSE 0 END
- VAR_YoY = SALES_CY - SALES_PY
ISSUES
The columns above work as long as the user doesn't toggle the date range of the dashboard however if the user switches to a last 12 month view, the visual is no longer correct because date_yoy is only showing data in the current calendar year.
What am I missing here ? I'm hoping to achieve this in my existing transaction table and not have to create an additional table via Magic ETL. In reality due to volume creating a table of unique combinations of product, customer, and location for every date in the last 365 days would produce upwards of 500 million rows which to me just seems like a waste of credits / storage for something as simple as what I'm trying to achieve.
I understand Domo isn't PowerBI but this would be as simple as 3 measures without any additional etl or tables because of the relational data tables in a model.
- Sales = sum(sales)
- sales py = calculate(sum(sales),sameperiodlastyear(date))
- var yoy = sales - sales py
Question
What is the best practice in Domo to achieve the chart below that DOESN'T break when the date range isn't current year? I'm really hoping I'm just not aware of the beast mode formula to achieve this and the the answer isn't creating a giant table via magic.
Thanks
