I am generating a number of reports that will require trailing 12 month calculations, presented in a trend report by month. In summary, for each of the last 12 months, I need to display the prior 12 months Revenue. I have brought the data into my query and I can very simply calculate Trailing Twelve month figures using
Runningsum([Amount_Rev]) - Runningsum(previous([Amount_Rev],12))
as long as I do not filter out the prior 12 months from my report.
At the end of the day, we do not want to display the last 24 months, so I will filter the block/report down to only show the last twelve. Heres the rub - I can apply the T12 filter to the block, but then my rolling running sum calculations will not be able to reference the data from month -13 to -24.
I have a work around - I have used a drillfilter to apply my T12 filter, and then in my calculation I have modified it to say:
Nofilter(Runningsum([Amount_Rev])-Runningsum(previous([Amount_Rev];12));Drill)
That seems to work, but I don't like using the drill filter for this purpose. We likely will turn on drill, and I do not want the users to be able to manipulate the T12 filter when looking at the filter bar.
Is there a way to use a formula similar to Nofilter([Measure],Block)?