Downloads
7931.zip

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.