July 2003 MDX Puzzle Solution

Problem: Use the FoodMart 2000 Sales cube to create an MDX query that finds the most recent nonzero sales amount for each customer in Los Angeles for each quarter of 1997. Format the quarters of 1997 on the columns and the individual customers in Los Angeles on the rows. In each cell, display the most recent monthly Store Sales value for that customer in that quarter. Here's a hint: This problem is very much like an inventory problem in which you want to aggregate the last known value for each time period. You need to create a measure that aggregates along the Time dimension as the last nonempty child.

Solution: To solve this problem, I created two calculated members, as Listing A shows. LastSales determines whether the CURRENTMEMBER of the Time dimension is the leaf level (e.g., months). If so, LastSales returns the store sales for that time period. If the time period isn't at the month level, LastSales uses the NonEmptySales calculated member to return the store sales for the first nonempty, month-level time member in that time period. The NonEmptySales measure filters out all the month-level time members that don't have a Store Sales value. NonEmptySales then returns the Store Sales value for the most recent month (the last month) left after filtering the empties.