Skip to content

    • Beast Mode

    Beast Mode

    Calculating period over period change in Domo

    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:

    Welcome!

    It looks like you're new here. Members get access to exclusive content, events, rewards, and more. Sign in or register to get started.
    Sign In

    Best Answer

    • Coach
      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

    • 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)

    • Coach
      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

    • 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.

    Welcome!

    It looks like you're new here. Members get access to exclusive content, events, rewards, and more. Sign in or register to get started.
    Sign In

    Welcome!

    It looks like you're new here. Members get access to exclusive content, events, rewards, and more. Sign in or register to get started.
    Sign In

    Quick Links

    Categories