<img height="1" width="1" style="display:none" src="https://www.facebook.com/tr?id=612681139262614&amp;ev=PageView&amp;noscript=1">
Skip to content

Need help? Talk to an expert: phone(904) 638-5743

MDX Expressions Cheat Sheet

Problem Expression
Simple Gross Profit Calculation
 
        [Measures].[Sales Amount] - [Measures].[Total Product Cost] 
 
Sales in the USA

([Measures].[Sales Amount], [Customer].[Country].&[United States])                       
 
Year to Date Sales (Works for any level of Date Hiearchy)

 

         Aggregate(
        
               PeriodsToDate( [Date].[Calendar Hierarchy].[Year],
        
               [Date].[Calendar Hierarchy].CurrentMember ),
        
               ([Measures].[Sales]))

 
Alternate Year To Date Expression (YTD, QTD, MTD)
 
       AGGREGATE (

YTD ([Date Order].[Calendar].CurrentMember),

Measures.[Sales Amount])
 
 
Product Ranking
 
IIF (Product.Product.CurrentMember IS Product.Product.[All],NULL,

IIF (Measures.[Sales Amount] = 0, NULL,

RANK(Product.Product.CurrentMember, ORDER (Product.Product.Members, Measures.[Sales         Amount]))))
 
 
Sales from 365 Days Ago

(ParallelPeriod([Invoice Date].[Date Hierarchy].[Date], 365, [Invoice Date].[Date
Hierarchy].CurrentMember), [Measures].[Sales Amount])
   
     
Sales in the Previous Period

(Measures.[Sales Amount], [Date Order].[Calendar].CurrentMember.PrevMember)
 
 
Top 10 Selling Products (Named Set)

TopCount (Product.Product.Product.Members,10,Measures.[Sales Amount])
 
Three Years Average Sales From NOW()

 

         Avg(

{ParallelPeriod( [Date].[Date].[Year], 3,

StrToMember("[Date].[Date].&[" + Format(now(), "yyyyMMdd") + "]")) :

StrToMember("[Date].[Date].&[" + Format(now(), "yyyyMMdd") + "]")} , [Measures].[Sales         Count])

 

Drill-through Action Caption
 
       'Get Sales Details for'

+[Product].[Product].CurrentMember.Member_Caption
 

 
Change SSAS Calculation Text color
        
        IIF([Measures].[Profit Percentage] < .40, 255 , 0) 

 
Changing a Calculation with a SCOPE statement
 
        SCOPE ([Measures].[Scope Profit]);

THIS = ([Measures].[Sales Amount] - [Measures].[Standard Product Cost]);

END SCOPE;
 

 
Clear Ration Value when at all level
    
        SCOPE ([Customer].[Customer Geography].[All], Measures.RatioOverParent);

THIS = NULL
 
 

 
SSAS KPI Value Expression
        
        [Measures].[Sales Amount] * 1.2  
 
 
SSAS KPI Goal Expression
        
       Case

When IsEmpty

(ParallelPeriod

([Date Order].[Fiscal].[Fiscal Year],

1,[Date Order].[Fiscal].CurrentMember))

Then [Measures].[Sales Amount]

Else 1.10 *

([Measures].[Sales Amount],

ParallelPeriod([Date Order].[Fiscal].[Fiscal Year],

1,[Date Order].[Fiscal].CurrentMember))

End
 
 
SSAS KPI Status Expression
 
        
        Case

When KpiValue( "Sales Revenue YTD" ) / KpiGoal( "Sales RevenueYTD" ) > 1

Then 1

When KpiValue( "Sales Revenue YTD" ) / KpiGoal( "Sales Revenue YTD" ) <= 1 And KpiValue( "Sales Revenue YTD" ) / KpiGoal( "Sales Revenue YTD" ) >= .85

Then 0

Else -1

End
 
 

 
SSAS KPI Trend Expression
 
        
       Case

When IsEmpty

(ParallelPeriod

([Date Order].[Fiscal].[Fiscal Year],

1,[Date Order].[Fiscal].CurrentMember))

Then 0

When VBA!Abs

((KpiValue( "Sales Revenue YTD" )- (KpiValue( "Sales Revenue YTD" ),

ParallelPeriod( [Date Order].[Fiscal].[Fiscal Year],

1, [Date Order].[Fiscal].CurrentMember)))

/(KpiValue( "Sales Revenue YTD" ),

ParallelPeriod

( [Date Order].[Fiscal].[Fiscal Year],

1,[Date Order].[Fiscal].CurrentMember))) <=.02 Then 0 When (KpiValue( "Sales Revenue YTD" )- (KpiValue( "Sales Revenue YTD" ), ParallelPeriod ( [Date Order].[Fiscal].[Fiscal Year], 1, [Date Order].[Fiscal].CurrentMember))) /(KpiValue( "Sales Revenue YTD" ), ParallelPeriod( [Date Order].[Fiscal].[Fiscal Year],1,[Date Order].[Fiscal].CurrentMember)) >.02

Then 1

Else -1

End