Calculating period over period change in Domo

Strostle · 2024-09-12T18:36:04+00:00

There was an error rendering this rich post.

I've been trying to find a solution for easily calculating percent change of metrics between two periods and put them in a table. Like "What is the percent change for "costs" between this month, and last month?"

Normally this is easy to calculate outside of Domo using the standard formula:
(V2 - V1) / V1) *100

However, translating this into a beast mode for Domo doesn't seem possible. There are some cards that do show percent change, but they are very rigid, and limited to one metric as a scorecard or byline on a line graph.

Understanding how all your key metrics have changed since last month in a single table feels like a basic request. Is it really not possible create this fundamental type of reporting in Domo's visualizer?

Find more posts tagged with

Visualization

Beast Mode

Accepted answers

ggenovese

If you have more than one year of data you'll need to account for the year, and not only the month. You can use the LAST_DAY() function to normalize your dates:

— % Change Costs

(

— current month costs

SUM(CASE WHEN LAST_DAY(`Your Date Field`) = LAST_DAY(CURRENT_DATE()) THEN `Costs` END)

-

— prior month costs

SUM( CASE WHEN LAST_DAY(`Your Date Field`) = LAST_DAY(DATE_SUB(CURRENT_DATE() INTERVAL 1 Month)) THEN `Costs` END)

)

/

— prior month costs

SUM( CASE WHEN LAST_DAY(`Your Date Field`) = LAST_DAY(DATE_SUB(CURRENT_DATE() INTERVAL 1 Month)) THEN `Costs` END) * 100

All comments

nmizzell

hey strostle,

POP is very simple in domo using the case statement, date sub, and current_date() functions:

sum(case when month(date) = month(date_sub(current_date(), interval 1 month)) then 'revenue' end) / sum(case when month(date) = month(current_date()) then 'revenue' end)

ggenovese

If you have more than one year of data you'll need to account for the year, and not only the month. You can use the LAST_DAY() function to normalize your dates:

— % Change Costs

(

— current month costs

SUM(CASE WHEN LAST_DAY(`Your Date Field`) = LAST_DAY(CURRENT_DATE()) THEN `Costs` END)

-

— prior month costs

SUM( CASE WHEN LAST_DAY(`Your Date Field`) = LAST_DAY(DATE_SUB(CURRENT_DATE() INTERVAL 1 Month)) THEN `Costs` END)

)

/

— prior month costs

SUM( CASE WHEN LAST_DAY(`Your Date Field`) = LAST_DAY(DATE_SUB(CURRENT_DATE() INTERVAL 1 Month)) THEN `Costs` END) * 100

Strostle

Thanks ggenovese, your solution worked and I appreciate the added detail. I wish Domo had a more natural way to produce these results when multiple metrics are on a table, but I think setting up some variables and/or additional beast modes will make that possible.