Hook (1-2 sentences): When you filter a Domo card to a single month, the underlying SQL query excludes every other month — including the previous one you need for comparison. Here's the ETL workaround that solved this problem before Variables, FIXED functions, and Segments existed.
Why It Matters
Without a workaround, applying a month filter in Domo is a one-way door: you get your selected month's data, and nothing else. That makes month-over-month comparisons impossible in a single card unless you hard-code date ranges — which breaks the moment anyone touches the filter. This ETL pattern gives analysts a way to embed comparison periods directly into the dataset, so the card always has what it needs regardless of what the filter selects.
What You'll Learn
- Understand why Domo's single-SQL-query architecture makes filtered date comparisons hard
- Use ETL Union steps to create offset copies of a dataset tagged by period
- Hardcode date offsets (e.g., +31 days) to shift data into a comparable period slot
- Structure your dataset so Beast Mode calculations can distinguish "actual" from "offset" rows
- See how this old-school approach relates to modern tools like Variables and the FIXED function
Building Month-over-Month Comparisons in ETL Before the Modern Tooling Existed
The root of the problem is architectural: Domo issues a single SQL query per card, and any filter you apply becomes a WHERE clause. Filter to June, and the query literally cannot see May. No Beast Mode or card-level trick can reach data that's been excluded at the query level.
The ETL workaround sidesteps this by duplicating your dataset at the data layer, before the card ever runs a query. The approach works in three steps:
-
Keep your actuals as-is. Take your source dataset and label each row with a period tag — "actual" — and leave the dates untouched.
-
Create offset copies using Union. Duplicate the dataset in ETL, then shift the date column forward by a fixed number of days (31 days for a one-month offset, ~62 for two months). Tag these rows with a period label like "1 month offset" or "2 month offset." Union them back onto the actuals.
-
Let the filter do the work. Now when a user filters to May, the query returns actual May rows and the shifted April/March rows that were moved into May's date range. Your Beast Mode can then segment by the period tag to plot each series separately.
The tradeoff is bluntness — hardcoding 31 days is imprecise for months of different lengths, and the approach gets brittle if your date logic is complex. It also means your dataset is 2x or 3x the size of the original. But for straightforward transaction data where approximate offsets are acceptable, it works reliably and requires no premium Domo features.
This pattern is worth understanding even if you're now using Variables or FIXED — knowing why those tools were built makes you sharper at choosing between them when your use case is on the boundary.
