MDX calculation for 12 rolling months

Question

I'm having issues trying to create a MDX calculated member inside a SSAS cube, that will show the aggregation for the previous 12 months (excluding the current month), based on a date from Calendar hierarchy.

I have found this post, however, because in my case the base measure is a DistinctCount aggregation, the YTD() function does not work by adding Aggregate() - it always displays NULL - and by adding Sum() it will display a RollingSum, which is incorrect.

whytheq whytheq · Accepted Answer · 2015-12-03T16:30:19

This is against a DISTINCT COUNT measure and functions ok - although it is understandably slow:

WITH 
  MEMBER [Measures].[ytdCalc] AS 
    Aggregate
    (
        Head
        (
          Descendants
          (
            Ancestor
            (
              [Date].[Date - Calendar Month].CurrentMember
             ,[Date].[Date - Calendar Month].[Calendar Year]
            ).Item(0).Item(0)
           ,[Date].[Date - Calendar Month].[Calendar Month]
          )
        ).Item(0).Item(0)
      : 
        [Date].[Date - Calendar Month].CurrentMember
     ,[Measures].[Num People]
    ) 
SELECT 
  {
    [Measures].[ytdCalc]
   ,[Measures].[Num People]
  } ON 0
 ,[Date].[Date - Calendar Month].[Calendar Month] ON 1
FROM [aCube];

YTD works fine for me. I have used it like this:

WITH 
  MEMBER [Measures].[ytdCalc] AS 
    Aggregate
    (
      YTD([Date].[Date - Calendar Month].CurrentMember)
     ,[Measures].[Num People]
    ) 
SELECT 
  {
    [Measures].[ytdCalc]
   ,[Measures].[Num People]
  } ON 0
 ,[Date].[Date - Calendar Month].[Calendar Month] ON 1
FROM [aCube];

MDX calculation for 12 rolling months

1 Answers