Use “descendants” in MDX

In the sample today I will show you how to use the “descendants” function in MDX. In this sample I want to count how many days and months I have sold stuff. And then calculate the average sales per month and date.

  Count ( Descendants ( [Ship Date].[Calendar].CurrentMember, [Ship Date].[Calendar].[Date] ) )

MEMBER MonthWithSales AS
  Count ( Descendants ( [Ship Date].[Calendar].CurrentMember, [Ship Date].[Calendar].[Month] ) )

MEMBER [Avg Sales Per Month] AS
  divide ( [Measures].[Internet Sales Amount], [Measures].[MonthWithSales] )

MEMBER [Avg Sales Per Day] AS
  divide ( [Measures].[Internet Sales Amount], [Measures].[DaysWithSales] )

SELECT NON EMPTY [Ship Date].[Calendar].[Calendar Year].&[2013].Children ON 1,
   [Measures].[Internet Sales Amount], 
   [Measures].[Avg Sales Per Month], 
   [Measures].[Avg Sales Per Day] 
} ON 0
FROM [Adventure Works]

And the result will look like this


“Descendants” have lots of different parameters. Please check them out on MSDN 🙂