No. MDX Cheat Sheet - Pragmatic Works

MDX Cheat Sheet

Home Resources MDX Cheat Sheet
MDX Expression Cheat Sheet
Problems Calculation 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
```
```