Here's an easy but useful puzzle. Frequently, when you view a measure's values over time in a line chart, you can see a lot of variation. You might have a hard time determining the overall trend in the values. To even out the "noise" in a trend line, you can use a rolling average. For example, if you're viewing sales for the last 12 months, the number might bounce up and down, and the trend line would look like a lightning bolt. However, if you display each monthly value as an average of that month's value and the previous 2 months' values, the curve will be smoother and the trend will be easier to detect. Use the FoodMart 2000 Sales cube to write an MDX query that displays the monthly Store Sales values for 1997 as a rolling 3-month average.
The solution in Listing A includes a calculated member called Rolling Sales. This query calculates Rolling Sales by averaging Store Sales for the past three time periods. The LASTPERIODS() function is handy for determining the past time periods that lead up to the current time member. I put both the Rolling Sales and the Store Sales measures on the rows so that they'd be easy to graph as a line chart.