September 2002 MDX Puzzle Solution

Using the FoodMart 2000 Sales cube, write an MDX query that displays each product category and its average month-to-month store sales growth for 1997. Place the product categories on rows in descending order regardless of their product department groupings. In other words, the product category with the best average monthly growth rate in 1997 should be the first row displayed in the result. Format the average growth rate as a percentage with two decimal places of accuracy.

I solved this problem by first creating a set of all the months in 1997 except January. I omitted January so that I could calculate the growth of each month in the set by comparing it to the previous month. I also created the measure Growth, which determines the growth in Store Sales from the previous month to the current, selected month. The MDX query in Listing A calculates and formats the growth as a percentage. Next, the code calculates the average growth rate by applying the Growth measure to all the months in the Months set and averaging the result. Finally, the query orders the Product Categories by their average growth rate. The MDX executes the sort with the break descending (BDESC) option so that the product categories are sorted independently from their product family or product department.