I would like to create a rolling month sum calculation in a data cube for data that has a time dimension of Year Month.
I need to do 12 months but as an example, a rolling 3 month would be the last three months including the current month, image below. I can’t see how to do this, is it possible? Thank you.
You could use the period of period feature to facilitate something like this.
You could create two period over period measures - one for 1 month back, and one for 2 months back. This would get you three months worth of data points on what single data point. From there, you can Add the 3 measures up to get the desired result.
I actually need to do a rolling 12 months so I was hoping there was an easier way than doing POP 11 times. Ok, thank you for confirming that there is not something that I am missing.
Ouch. Yeah, that definitely stings, but you would essentially need to add up 12 different values anyways. It just kinda sucks that they’re all on the metric set and might clutter things up.
I’ll definitely bring this up with our data team and see if there are enhancements we can make to streamline this kind of use case.
Yeah, I was hoping that there was something like SUM but with a LAST type filter to limit it to the rolling 12 entries of a year/month time dimension. Or something like MOAVG where you can put in the number of entries. It may be difficult because maybe the system wouldn’t necessarily know if there was a missing month of data. In any case, thanks for looking at it. POP is a good workaround.
Hi Kelly,
One suggestion could be to just use the MOVAVG formula and then multiple by the number of periods.
Like this (3-month example):
It does work but my only issue was when trying to show the dates in descending order, it does not work. It is a quicker solution though, I will just have to determine if it is feasible making the users scroll through lots of previous months. Thanks!
If you want to have the dates in descending order, but still use my suggestion you can do the following (again 3-month example):
First add a formula: MOVAVG($Amt$,3)*3
Lets name this one ‘Rolling3MoSumTmp’
Add a second formula that is referencing only the result from 1): $Rolling3MoSumTmp$
Then click in the formula bar to set the offset value to 2
Hide the formula from step 1
Result:
WOW!! @ole.christian.valstad, thank you very much! That gives me the correct data although I’m not quite sure how. 
I really appreciate it!!
