+3I have a single that needs to essentially aggregate a rolling sum (of that last n months) of another metric.
The manual method I have found is:
TargetMetric + TargetMetric[SELECT: Month -1] + TargetMetric[SELECT: Month -2]
(I am incuding the current month in the “last” three months calculation)
But if I want to change this to the last 5 months or the last 24 months, I would need to add an n number of selects.
How Can I formulaically drive this calculation?
Best answer by oliverlee
+12
+12As a follow up on that: in case you want your integer N to be formula driven, MOVINGSUM will not be the solution, as it doesn’t allow to come from a dimensioned metric.
In this case one solution could be to first cumulate the data and then calculate the difference between a month and N months before that one.
For example as seen in this screenshot:
The SET # of Rolling Months determines the # of rolling months for each version. The TargetMetric is where the data is in. TargetMetric Cumulate is a simple Cumulate formula. Finally, the TargetMetric Rolling consists of this formula:'TargetMetric Cumulate'
- 'TargetMetric Cumulate'[BY CONSTANT: SHIFT(Month, -'SET # of rolling months')]
Enter your E-mail address. We'll send you an e-mail with instructions to reset your password.
