fbpx

MDX Cheat Sheet

Home Resources MDX Cheat Sheet
MDX Expression Cheat Sheet
ProblemsCalculation 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])
                                
                            
Drillthrough 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