Hello! I would like to calculate a movingsum formula where the starting month is always fixed (eg Jun 18) but the last month is always the current month. So for Mar 22 I would like the cumulative sum from Jun 18 to Mar 22 but for Apr 22 I would like the sum from Jun 18 to Apr 22. How can I do this?
Best answer by NathanView original
Does your data start on Jun 18?
You can use the CUMULATE function to keep a rolling tab of the total
My data start on Jan 15 but maybe I could create a metric to make zero all data before Jun 18 so that CUMULATE function is based on this one. However even when I try to do CUMULATE (metric 1, metric 2) where metric 1 is the free cash flow & metric 2 the month, from month 1 it brings a huge amount. When trying to put the BY dimension it produces an error. Is there any advice when using this formula how we should be handling dimensions?
you could try like this:
cumulate(metric[filter:Month.Start Date >= DATE(2028,01,01),Month]
ps: would be better to put the date in a metric itself
try to do this formula in the playground so you’ll see the dimensions being computed
@Nathan thanks for the advice! I try to do it exactly as you say and I get Syntax error: cannot validate formula. Is there any particular to watch out in terms of the dimensions in my metrics? Start date is a property of my month dimension. So what date do you suggest to put in a metric itself?
Sorry I wrote it incorrectly:
cumulate(metric[filter:Month.'Start Date'>= DATE(2028,01,01)],Month)
In terms of dimensions, all you need is for your source metric to have the Month dimension