Skip to main content

I am trying to get the prior year total to then multiply it by a growth rate.  (Prior Year * (1 + Growth Rate).

PREVIOUS( ) is not working because I do not have Year in the metric, and trying to add it was not working.

GrowthRate? is a Boolean that has TRUE starting in January 2026 and all periods forward

OriginalValue is a Number metric that has values monthly from January 2024 and all periods forward

 

What I’m trying to get is something like this:

IF(GrowthRate?,  (Prior Year * (1 + Growth Rate), OriginalValue).  I also can’t calculate off the OriginalValue metric because the Growth Rates will compound.  If all years in OriginalValue were $1000 and I am adding 10% Growth I would want to see $1000, $1100, $1210, etc...

Unfortunately my 2025 occasionally has some 0 seasonality that is throwing off other metrics after this one.
 

  Jan Feb Mar Apr May Jun Jul Aug Sep Oct Nov Dec
2025 1000 1000 0 0 0 1000 1000 1000 1000 1000 1000 1000
2026 =9000 * (1 + 10%) /12 =9000 * (1 + 10%) /12 =9000 * (1 + 10%) /12 =9000 * (1 + 10%) /12 =9000 * (1 + 10%) /12 =9000 * (1 + 10%) /12 =9000 * (1 + 10%) /12 =9000 * (1 + 10%) /12 =9000 * (1 + 10%) /12 =9000 * (1 + 10%) /12 =9000 * (1 + 10%) /12 =9000 * (1 + 10%) /12
 

825

 825 825 825 825 825 825 825 825 825 825 825

 

Hi,

To put the value of PY on CY you can do this:

source[BY SUM: Month.Year] // sums the months into their year
[BY CONSTANT: Month.Year-1] // allocate the value of year -1 onto months of the year

Hi,

To put the value of PY on CY you can do this:

source[BY SUM: Month.Year] // sums the months into their year
[BY CONSTANT: Month.Year-1] // allocate the value of year -1 onto months of the year

The source I want is the metric I’m calculating, so the BY modifier is not working.  I was using PREVIOUS(Month, 12), but I need the FY amount not just 1 month at a time.  The math still works out, but I want to apply the growth and then spread the FY amount evenly across 12 months.
 

 

Found a solution, even if not the most pretty.

  • Kept this Metric #1 as is because it was calculating the correct totals, just wrong by month.
  • Created Metric #2 to Metric #11BY SUM: Year]
  • Created a third metric with the same formula as the first, but replaced the PREVIOUS(Month, 12) with Metric #22BY SPLIT: Month.Year]

Hey Ben,

I do think splitting in several metrics is the best way actually. Your formula is leaner.

Though you should be able to do the [by] on previous() like: previous(Month)[By:Month.Year]

that’s a recent update

Reply


Ask your question here!

Terms & ConditionsCookie settings