So I’ve got a table of year, month and a value. I’m trying to calculate for each month what the trailing 12 months of calculations are. Here’s an example of what I’m trying to get (mocked up in Excel):
Where each cell in the trailing 12-months is the sum of the current month and the 11 months before it. Is there any great way to do it than using a 11 Lag() functions and offsetting them 1-11?
And this doesn’t seem to work… It nulls out a lot of the values.
[Revenue] + Lag([Revenue]) + Lag([Revenue], 2) + Lag([Revenue], 3) + Lag([Revenue], 4) + Lag([Revenue], 5) + Lag([Revenue], 6) + Lag([Revenue], 7) + Lag([Revenue], 8) + Lag([Revenue], 9) + Lag([Revenue], 10) + Lag([Revenue], 11)
EDIT: Nevermind, it doesn’t work in grouped tables. If I create a sub-table off that then it seems to work.
Depending on how your data is grouped and sorted, I think that MovingSum would work well.
MovingSum(number field, above, below)
You can specify the column you want to sum, then define the number of rows above and/or below you want to include in that sum.
MovingSum is what is called a window function, which means it takes into account how the table it is applied to is arranged. Because it looks at the relative position of rows, it means that you must have a unique sort on the table or you won’t get consistent results. It also takes into account how your data is grouped, and the function won’t cross groups. If you group by year, it will only sum rows in that year.
Once you get your table set up properly, it’s a very powerful function that seems like it would handily solve your problem here.
Lag is also a window function, so that’s why it doesn’t cross groups. Your formula is the long had version of