January MDX Puzzle Solution Revealed - 05 Jan 2000


Last month's question asked: Using the FoodMart Sales cube, what are the unit sales of the states in the United States for each of the most recent 3 months that have data? The query in Listing A is the answer.

Multiple solutions will work, but I chose calculated sets because this solution demonstrates the use of sets and how to index into a set to return a particular set item. The first set, \[Recent Months\], uses the filter function to collect all the months that have data. The second set, \[Recent Month\], uses the item property to index into the first set to retrieve the most recent month. \[Recent Month\] is a set containing a single month.

This query displays the most recent three months in reverse order on the columns. It uses the item property to retrieve the first item in the \[Recent Month\] set. Then it uses the Lag function to retrieve the adjacent months.

Hide comments


  • Allowed HTML tags: <em> <strong> <blockquote> <br> <p>

Plain text

  • No HTML tags allowed.
  • Web page addresses and e-mail addresses turn into links automatically.
  • Lines and paragraphs break automatically.