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:

some category (City in this example but this column will eventually dynamically change based on selected parameter in SSRS),
some measure for the category (Internet Sales in this example) for a specific Month
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] )

jonas jonas · Accepted Answer · 2017-09-27T12:05:44

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];

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

1 Answers