Solved

Moving sum fixed start date, dynamic end date

  • 27 May 2022
  • 7 replies
  • 108 views

Userlevel 2
Badge +1

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? 

icon

Best answer by Nathan 30 May 2022, 20:53

View original

7 replies

Userlevel 2
Badge +2

Does your data start on Jun 18?

You can use the CUMULATE function to keep a rolling tab of the total

 

 

Userlevel 2
Badge +1

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?

Userlevel 4
Badge +4

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

Userlevel 4
Badge +4

try to do this formula in the playground so you’ll see the dimensions being computed 

Userlevel 2
Badge +1

Hi @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?

Userlevel 4
Badge +4

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

Userlevel 4
Badge +4

@Marina did it work ?

Reply