Calculating period over period change in Domo

Strostle
Strostle Member
in Beast Mode

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?

Tagged:

Best Answer

  • ggenovese
    ggenovese Contributor
    Answer ✓

    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

Answers

  • nmizzell
    nmizzell Contributor

    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
    ggenovese Contributor
    Answer ✓

    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
    Strostle Member

    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.

Categories