MDX Time Expressions User Handbook

MDX (Multidimensional expressions) is a query language for OLAP (Online analytical processing) services using database management systems. It is a highly functional expression syntax that is used for querying multidimensional data
This cheat sheet provides you with the expression syntax and the statements required to learn SSAS in-depth and work on it.

Kick-start your career in MSBI with the perfect MSBI Training Course now!


You can also download the printable PDF of this cheat sheet.
MDX Time Expressions Cheat Sheet

MDX Time Expressions:

StatementExpressions
To get previous month in the Time dimensionSELECT ClosingPeriod([Date].[Calendar].[Month], [Date].[Calendar].DefaultMember) ON 0
FROM [Sales Summary]
WHERE ([Measures].[Sales Amount])
To display the first day of last month in the cubeSELECT OpeningPeriod([Date].[Calendar].[Date]
, ClosingPeriod([Date].[Calendar].[Month], [Date].[Calendar].DefaultMember)
) ON 0
FROM [Sales Summary]
MDX query to get the last month loaded in the cubeSELECT ClosingPeriod([Date].[Calendar].[Month], [Date].[Calendar].DefaultMember) ON 0
FROM [Sales Summary]
MDX query that uses execution date/time as a parameterSELECT {[Measures].[Internet Order Count]} ON 0
, {StrToMember(“[Date].[Date].[”
+ Format(now(), “MMMM dd, yyyy”) + “]”)
} ON 1
FROM [Direct Sales];
To get the latest months and previous years same month dataSELECT {ClosingPeriod([Date].[Calendar].[Month], [Date].[Calendar].DefaultMember)
, ParallelPeriod([Date].[Calendar].[Calendar Year]
, 1
, ClosingPeriod([Date].[Calendar].[Month], [Date].[Calendar].DefaultMember)
)
} ON 0
FROM [Sales Summary]
To create a calculated member for AVG sales over last 3 years based on NOW () functionCREATE MEMBER CurrentCube.Measures.[Avg3Years] AS
Avg(
{ParallelPeriod( [Date].[Date].[Date Yr], 3, StrToMember(“[Date].[Date].&[” + Format(now(), “yyyyMMdd”) + “]”))
: StrToMember(“[Date].[Date].&[” + Format(now(), “yyyyMMdd”) + “]”)}
, [Measures].[Sales Qty]
) ;
To get the Previous year to date valuesWITH MEMBER [Measures].[Current YTD] AS
SUM(YTD([Date].[Calendar].CurrentMember), [Measures].[Internet Order Quantity])
MEMBER [Measures].[Last YTD] AS
SUM(YTD(ParallelPeriod([Date].[Calendar].[Calendar Year]
, 1
, [Date].[Calendar].CurrentMember))
, [Measures].[Internet Order Quantity]
)
SELECT {[Measures].[Current YTD]
, [Measures].[Last YTD]
} ON 0
FROM [Adventure Works]
WHERE ([Date].[Calendar].[Date].[March 22, 2004])
To calculate monthly average of one yearWITH MEMBER [Measures].[AvgVal] AS
Avg( Descendants([Date].[Calendar].[Calendar Year].&[2004]
, [Date].[Calendar].[Month])
, [Measures].[Internet Order Count]
)
SELECT {[Measures].[AvgVal]} ON 0
FROM [Adventure Works]
WHERE ([Product].[Product Model Lines].[Model Name].&[Classic Vest])
To return lists of months from start of year to specified monthSELECT YTD([Date].[Calendar].[Month].&[2003]&[8])
ON 0
FROM [Sales Summary]
To order the date dimension members in descending order in a reportSELECT {[Measures].[Reseller Order Quantity]} ON 0
, ORDER(Tail([Date].[Calendar].[Calendar Year].Members,3)
, [Date].[Calendar].CurrentMember.Member_Key, DESC
) ON 1
FROM [Adventure Works]
To load the first month of last year in the cubeSELECT OpeningPeriod([Date].[Calendar].[Month]
, ClosingPeriod([Date].[Calendar].[Calendar Year], [Date].[Calendar].DefaultMember)
) ON 0
FROM [Sales Summary];
MDX query to show the year level data for all years except the last one, and month level data for the last yearSELECT {NULL:
ClosingPeriod([Date].[Calendar].[Calendar Year], [Date].[Calendar].DefaultMember).PrevMember
, DESCENDANTS(ClosingPeriod([Date].[Calendar].[Calendar Year], [Date].[Calendar].DefaultMember)
, [Date].[Calendar].[Month])
}
ON 0
FROM [Sales Summary]
To calculate sales for 12month to date in MDXWITH MEMBER [Measures].[Last 12 Mth Order Count] AS
SUM(
ClosingPeriod([Date].[Calendar].[Month], [Date].[Calendar].[All Periods]).Lag(12)
: ClosingPeriod([Date].[Calendar].[Month], [Date].[Calendar].[All Periods])
, [Measures].[Order Count]
)
SELECT [Measures].[Last 12 Mth Order Count] ON 0
FROM [Adventure Works]
To get count of months with sales amount > 0 in defined periodWITH Member [Measures].[Months With Above Zero Sales] AS
COUNT(FILTER(
DESCENDANTS({[Date].[Calendar].[Calendar Year].&[2003]: [Date].[Calendar].[Calendar Year].&[2004]}
, [Date].[Calendar].[Month])
, [Measures].[Sales Amount] > 0
)
)
SELECT {[Measures].[Sales Amount], [Measures].[Months With Above Zero Sales]} ON 0
, [Product].[Product Model Lines].[Product Line].Members on 1
FROM [Adventure Works]
WHERE ([Date].[Calendar].[Calendar Year].&[2003]: [Date].[Calendar].[Calendar Year].&[2004])
To calculate monthly average of a year including empty monthsWITH MEMBER [Measures].[AvgVal] AS
Avg( Descendants([Date].[Calendar].[Calendar Year].&[2004]
, [Date].[Calendar].[Month])
, CoalesceEmpty([Measures].[Internet Order Count], 0)
)
SELECT {[Measures].[AvgVal]} ON 0
FROM [Adventure Works]
WHERE ([Product].[Product Model Lines].[Model Name].&[Classic Vest])

Get ready for the high-paying MSBI jobs with these Top MSBI Interview Questions and Answers!

Download a Printable PDF of this Cheat Sheet

With this, we come to an end of the MDX Time Expression Cheatsheet. To get in-depth knowledge, check out our interactive, live-online SSAS certification Training here, that comes with 24*7 support to guide you throughout your learning period. Intellipaat’s SSAS online training equips you with various analysis techniques, discovery trends and building OLAP cubes for generating business insights.

Interested in learning MSBI? Click here to learn more in this MSBI Training in Bangalore!

Recommended Videos

Leave a Reply

Your email address will not be published. Required fields are marked *