I am trying to calculate a moving, cumulative sum of sales. Example:
|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!