MDX Expressions
Function Requires Returns
CurrentMember Dimension Member
PrevMember Member Member
NextMember Member Member
Lag Numeric Expression Member
Lead Numeric Expression Member
ParallelPeriod Level, numeric expression, member Member
PeriodsToDate Level, member Set
YTD Member Set
MTD Member Set
Sum Set, numeric expression Number
 
Navigating the Hierarchy
Function Requires Returns
Parent Member Member
Children Member Set
FirstChild Member Member
LastChild Member Member
Descendants Member, level or member, distance Set
Ancestor Member, level or member, distance Member
Siblings Member Set
FirstSibling Member Member
LastSibling Member Member
Cousin Member, ancestor member Member
 
Data Analysis
Function Requires Returns
Sum Set, numeric expression Number
Count Set Integer
Avg Set, numeric expression Number
Tail Set, count Set
Item Set, index number Member
OpeningPeriod Level, member Member
ClosingPeriod Level, member Member
Max Set, numeric expression Number
Min Set, numeric expression Number
 
Moving Averages
Function Requires Returns
Level Member Level
Members Level or dimension Setr
Iif Condition, what to do if true, what to do if false Number or string
 
Filters
Function Requires Returns
Filter Set, condition Set
IsEmpty Expression Boolean
 
Member Properties
Function Requires Returns
Properties Member name String
Val (VBA Function) String Numeric value
 
Aggregates
Aggregate function Returned value
Sum The sum of the input values
Min The lowest of the input values
Max The highest of the input values
Count The number of input values
Distinct Count The number of unique input values
 
Example MDX Calculations
Sales Growth 
([Time].CurrentMember, [Measures].[Sales])
- ([Time].CurrentMember.PrevMember, [Measures].[Sales])
PP Growth 
([Time].CurrentMember, [Measures].[Sales])
- (ParellelPeriod(Year, 1, [Time].CurrentMember), [Measures].[Sales])
YTD Sales 
sum(ytd([Time].CurrentMember), [Measures].[Sales])
YTD Sales Using PeriodsToDate 
sum(PeriodsToDate([Time].[Year], [Time].CurrentMember), [Measures].[Sales])
Product Percentage 
([Product].CurrentMember, [Measures].[Unit Sales])
/ ([Product].CurrentMember.Parent, [Measures].[Unit Sales]) * 100
Product Total Percentage 
([Product].CurrentMember, [Measures].[Unit Sales])
/ ([Product].[All Products], [Measures].[Unit Sales]) * 100
Average Stock - Brute Force 
Sum(Descendants([Time].CurrentMember, [Month]), [Measures].[Quantity])
/ Count(Descendants([Time].CurrentMember, [Month]))
Average Stock - Elegant 
Avg(Descendants([Time].CurrentMember, [Month]), [Measures].[Quantity])
Closing Period - Brute Force 
(Tail(Descendants([Time].CurrentMember, [Month]), 1).Item(0), [Measures].[Quantity])
Closing Period - Elegant 
(ClosingPeriod([Month], [Time].CurrentMember), [Measures].[Quantity])
Max for Period 
Max(Descendants([Time].CurrentMember, [Month]), [Measures].[Quantity])
Min for Period 
Min(Descendants([Time].CurrentMember, [Month]), [Measures].[Quantity])
Moving Average 
Avg([Time].CurrentMember.Lag(2):[Time].CurrentMember, [Measures].[Unit Sales])
Products Down 

Count(Filter(Descendants([Product].CurrentMember, [Product Name])
            , ([Time].CurrentMember, [Measures].[Unit Sales])
              < ([Time].CurrentMember.PrevMember, [Measures].[Unit Sales])))
/ 
Count(Descendants([Product].CurrentMember, [Product Name]))
Dynamic Custom Default Member 
Tail(Filter([Time].[Month].Members
     , Not IsEmpty([Time].CurrentMember)), 1).Item(0)
Properties Query 
Filter([Store].[Store Name].Members
       , Val([Store].CurrentMember.Properties("Store Sqft")) < 21000)
Products Down Query 
select   
    {{{[Measures].[Products Down]}   
      * {[Customers].DefaultMember}   
      * {[Product].DefaultMember}}} on columns  
    , {[Time].&[1998].&[Q1].&[1]  
       , [Time].&[1998].&[Q1].&[2]  
       , [Time].&[1998].&[Q1].&[3]  
       , [Time].&[1998].&[Q2].&[4]  
       , [Time].&[1998].&[Q2].&[5]  
       , [Time].&[1998].&[Q2].&[6]  
       , [Time].&[1998].&[Q3].&[7]  
       , [Time].&[1998].&[Q3].&[8]  
       , [Time].&[1998].&[Q3].&[9]  
       , [Time].&[1998].&[Q4].&[10]  
       , [Time].&[1998].&[Q4].&[11]} on rows  
from  
    [Sales_MDX2]
MDX Query 
select
    {[Measures].[Unit Sales]
     , [Measures].[MA]} on columns
    , {Descendants([Time].[Year].&[1997], [Month])} on rows
from
    [Sales_MDX2]