Getting the same measure in two columns side-by-side for different time periods in MDX

Question

need to produce a specific output in MDX and I am struggling to produce the desired result. I am trying to build an example using ADW Cube.

I need to produce a table where I have three columns:

1. some category (City in this example but this column will eventually dynamically change based on selected parameter in SSRS),
2. some measure for the category (Internet Sales in this example) for a specific Month
3. the same measure as in the second column but for a previous month

The table should be filtered only to the top three highest items based on the measure in the second column.

Below is some simple static example of what I got so far, but the idea is basically this: User chooses some values in parameters that will subset the data for which the table will be filtered (represented by [Country] (Canada) in the example). He will then choose the granularity level within some hierarchy for which he wants his results (represented by [City] in the example). And he will choose the desired period (Specific [Year] and [Month]). Upon running the query, he should get a similar table as below (where he has the top three results based on the second column):

Category      Internet Sales Amount (CurrentPeriod) Internet Sales Amount (PreviousPeriod)
Langford      $13,468.39                             null
Vancouver     $12,291.48                             $7,447.26
Sooke         $11,563.01                             null

The first column has represents the chosen granularity. The second column represents Internet Sales for the selected year-month combination for the given item and the third column represents Internet Sales in the month before the chosen combination.

Here is what I got so far. I was able to produce the first two columns of the table but I am struggling to include the third column as well. I come from SQL and am pretty new to MDX so bear with me if what I am asking seems simple or my approach so far is pretty off from how it should be done.

SELECT [Measures].[Internet Sales Amount] ON 0,  
TopCount  
   ({[Customer].[City].[City].Members}  
   , 3  
   , [Measures].[Internet Sales Amount]
   ) ON 1  
FROM [Adventure Works]  
WHERE ( [Customer].[Country].&[Canada], 
        [Date].[Calendar Year].&[2007], 
        [Date].[Month of Year].&[12] )

Answer 1

Okay, I managed to solve this simplified example.

WITH 
  MEMBER [Measures].[SalesPrevMth] AS 
    (
      [Measures].[Internet Sales Amount]
     ,[Date].[Calendar].CurrentMember.PrevMember
    ) 
SELECT 
  NON EMPTY 
    {
      [Measures].[Internet Sales Amount]
     ,[Measures].[SalesPrevMth]
    } ON COLUMNS
 ,NON EMPTY 
    TopCount  
     ({
        [Customer].[City].[City].Members
      * 
        [Date].[Calendar].[Month].ALLMEMBERS
      }, 3, [Measures].[Internet Sales Amount]) ON ROWS
FROM 
(
  SELECT 
    [Date].[Calendar].[Month].&[2007]&[12] ON COLUMNS
  FROM [Adventure Works]
)
WHERE [Customer].[Country].&[Canada];