Get count of invoices for last three months?

Question

Thank you all. This is my MDX query and this returns the count of invoices generated on March 2015 and works like a charm!

 WITH 
  SET [mySet] AS 
    [Customer].[Store Group].[Store Group] * {[Measures].[Sales #]} 
  MEMBER [Measures].[setDistCount] AS 
    DistinctCount([mySet]) 
SELECT 
  {
  [Measures].[Sales #]
  } ON 0
 ,NON EMPTY 
    [Customer].[Store Group].[Store Group] ON 1
FROM 
(
  SELECT 
    {[Calendar].[Month].[March 2015]} ON 0
  FROM [F1_SalesBI]
);

Output is like:

            Sales #
Store 1     156
Store 2      56
Store 3     546
...
Store 10     69

But I wish to get report like this:

            March     February     January
Store 1     156       656          145
Store 2      56        89           41
Store 3     546       215          215
...
Store 10     69        69           96

For my desired output, how am I supposed query? Please help me!

Answer 1

Generate looks like it is over-complicating the situation maybe something a little simpler is all that is required:

SELECT 
  {
   [Calendar].[Month].[March 2015],
   [Calendar].[Month].[February 2015],
   [Calendar].[Month].[January 2015],
  } ON 0
 ,NON EMPTY 
    [Customer].[Store Group].[Store Group] ON 1
FROM [F1_SalesBI]
WHERE [Measures].[Sales #];

To make this dynamic so it only ever shows the last three months in your cube then use the Tail function like this:

SELECT 
  Tail([Calendar].[Month].[Month].members,3) ON 0 //<< I've assumed this is ok but you may need to use [Calendar].[Month].members
 ,NON EMPTY 
    [Customer].[Store Group].[Store Group] ON 1
FROM [F1_SalesBI]
WHERE [Measures].[Sales #];