More Efficient YoY Actual to Budget View

Data_Devon · 2025-05-23T15:44:25+00:00

There was an error rendering this rich post.

Data_Devon

Hi Domo Sapiens,

Currently, we have a year over year graph that has current year budget also in the series:

The bar graphs are each year's actual values, and the line is the current year's budget value.

I hard-coded each series with a beastmode, as such:

CASE WHEN YEAR(Date) = '2025' AND Actual or Budget = 'Actual' THEN Charge Quantity

ELSE 0

END

CASE WHEN YEAR(Date) = YEAR(CURDATE()) AND Actual or Budget = 'Budget' THEN Charge Quantity

ELSE 0

END

Unfortunately, this renders the date filtering useless since each year is hardcoded in the above beastmode.

Best case scenario: I can use the Period over Period chart so that the Date can be filtered by the native "Choose Date" filter box. However, I can't figure out how to include the budget series when using a PoP chart, because it lumps it in with the year.

Any ideas?

Find more posts tagged with

Analyzer

Visualization

Dataset

Card Builder

Beast Mode

Accepted answers

DavidChurchman

Glad it helped. One note of caution: the problem with using BeastMode rather than ETL to force your data wide is it doesn't actually change structure of the data, just relabels it. Prior year data are still on different rows than current years. So if you try to do anything with your date variable, like filter it or graph-by, you'll lose the prior data, or it won't group in the way you're trying to group.

As a simplified example, this is basically what you're doing now (I think):

Date	Value	BeastMode Month	BeastMode Series
1/1/2001	10	January	2001
1/1/2002	20	January	2002
1/1/2003	30	January	2003
1/1/2004	40	January	2004
1/1/2005	50	January	2005
1/1/2006	60	January	2006
1/1/2007	70	January	2007

This is how I'm saying you should restructure your data:

Date	Value	Last Year's Value	Two years ago value
1/1/2003	30	20	10
1/1/2004	40	30	20
1/1/2005	50	40	30
1/1/2006	60	50	40
1/1/2007	70	60	50

The benefit being able to use the date variable in the graph-by and filter without losing the prior years data to compare against it, because it's all on the same row.

All comments

GrantSmith

Hi @Data_Devon - I'd recommend restructuring your data to allow more flexible period over period analysis. I've done a write up here with it: https://community-forums.domo.com/main/discussion/53481/a-more-flexible-way-to-do-period-over-period-comparisons#latest

Data_Devon

Thank you @GrantSmith !

I may have misunderstood your write up, but all the solutions you shared are for when you have one value but multiple periods assigned to the value.

I have the opposite problem - 1 date with multiple values assigned (budget and actual)

Did I misunderstand your write up?

GrantSmith

You'd have two separate beast modes for each different metric and the offset type

For example Current Year's Budget:

CASE WHEN `Period Type` = 'Current' AND `Actual or Budget` = 'Budget' THEN `Charge Quantity` END

Last Year's Budget

CASE WHEN `Period Type` = 'Last Year' AND `Actual or Budget` = 'Budget' THEN `Charge Quantity` END

You can then use these beast modes as Y-Axis values to display them on your chart. You'd likely need to define your own 2 year ago offset. This will then give you a single Date field you can use to leverage the date filtering and still display data relative to the filtered date.

Data_Devon

Awesome, thank you @GrantSmith . I think we are getting close. The flexibility of using the dynamic "CURDATE()" instead of hardcoding a date value is awesome.

In order to use the native "Choose Date" & "Graph By" filter that is native to each dashboard, I need to include the Date as the X Axis:

I think we're getting closer, but now I can't see the "year over year" view that I was originally hoping for - with each month next to the same month of the prior year.

I hope this makes sense. Thanks for your continued support.

DavidChurchman

I think I'm going to suggest something similar (identical?) to GrantSmith, but maybe easier because instead of flexible periods, it's just YoY.

Instead of a long dataset feeding your card that has all of your dates on top of each other, you can restructure your data to use just this year's data, with additional columns for past years. Then you can change the granularity and use real dates in your filter (this year's dates):

Here it is at a different granularity so you can see the dates are working:

I'm sure there are smarter ways to do this, but one approach is using MagicETL:

Use Domo's calendar dataset to get a list of every day in the year/years of interest. You can use a filter formula like year(dt) = year(currdate())
If not already aggregated by date, aggregate your current data by date, or if you don't want to aggregate it, create a column that is formatted as a date
Left join your data to the calendar data to get data for every day so far this year (and nulls for the other dates)
Add a year to the date in your data, rename columns, and left-join it again to get data for the prior year for all matching days this year. Now you should have something like: Date | This Year's Profit | Last Year's Profit
Repeat as needed for another year or years

Data_Devon

I like that @DavidChurchman - and that's closer to what I'm currently doing. I'm forcing a wide dataset by BeastMode-ing the "Last Year" records into their own columns. The only difference is that I also BeastMode the current year into wide format, thus rendering dates useless.

I think you provided the best solution though, so onward ⛵

DavidChurchman

As a simplified example, this is basically what you're doing now (I think):

Date	Value	BeastMode Month	BeastMode Series
1/1/2001	10	January	2001
1/1/2002	20	January	2002
1/1/2003	30	January	2003
1/1/2004	40	January	2004
1/1/2005	50	January	2005
1/1/2006	60	January	2006
1/1/2007	70	January	2007

This is how I'm saying you should restructure your data:

Date	Value	Last Year's Value	Two years ago value
1/1/2003	30	20	10
1/1/2004	40	30	20
1/1/2005	50	40	30
1/1/2006	60	50	40
1/1/2007	70	60	50

The benefit being able to use the date variable in the graph-by and filter without losing the prior years data to compare against it, because it's all on the same row.

Data_Devon

Amazing!! That's exactly what I'm doing and exactly the problem I ran into. Thank you @DavidChurchman and @GrantSmith for getting me where I needed to go.

I appreciate this community a ton!