Moving cumulative sums

Hi everyone!

I am trying to calculate a moving, cumulative sum of sales. Example:

monthly sales 3 2 5 1 2 6 4 3
cumulative 2-month sum 5 6 8 7

So, my data has monthly values and I am trying to calculate a dynamic 2-month cumulative sum (second row in the table). I would like to control the 2-month sum with a document property that I can also change to whatever I like, e.g. a 5-month cumulative sum.

I tried this on a bar chart where the x-axis is a year-month hierarchy:

Sum([SALES]) OVER (Intersect(Parent([Axis.X]),AllPrevious([Axis.X])))

However, this isn't solving the problem, because I want the break to be anywhere and not always in January.

Ideally, I would like to solve this without calculating new data columns but rather using a custom expression in the table (or bar chart).

Thanks a lot for helping me out!

(6) Answers