SSRS - floating sum (previous 5)

Question

In SSRS I have column group per days (user can change range). I need to evaluate for each day for the previous five days. I found PREVIOUS function which gives me only one previous value. Is there any option how to evaluate, for example, last 5 values (columns) in one group? Report is built upon SSAS cube. On cube I found TAIL function, but it doesn't work for me - I need "floating" evaluation for previous five days for each day.

Answer 1

You can add an expression in your textbox, something like this:

=Sum(IIF(CDate(DateAdd("d", -5, Today())) < CDate(Fields!YourDateField.Value), Fields!YourFloatingSum.Value, 0))

It adds just the values to the sum which are within the last five days from today on.

You can also change the Today() function to a Field to make it more dynamic. That depends how you need it in your report.