Time MDX Cheat Sheets
Contributors: Vidas Matelis, Thomas Ivarsson
Time MDX Cheat Sheet
| How do you get Last month in the time dimension SELECT ClosingPeriod([Date].[Calendar].[Month], [Date].[Calendar].DefaultMember) ON 0FROM [Sales Summary]WHERE ([Measures].[Sales Amount]); | Need a MDX query that returns list of months from start of year up to specified month. SELECT YTD([Date].[Calendar].[Month].&[2003]&[8])ON 0FROM [Sales Summary]; |
| I Need an MDX statement to show the first day of the last month in the cube SELECT OpeningPeriod([Date].[Calendar].[Date], ClosingPeriod([Date].[Calendar].[Month], [Date].[Calendar].DefaultMember) ) ON 0FROM [Sales Summary] | How in the report can I order date dimension members in descending order? SELECT {[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] |
| I Need an MDX statement to get the last Month loaded into a cube SELECT ClosingPeriod([Date].[Calendar].[Month], [Date].[Calendar].DefaultMember) ON 0FROM [Sales Summary]; | I Need an MDX statement to get the first month of the last year loaded into a cube SELECT OpeningPeriod([Date].[Calendar].[Month], ClosingPeriod([Date].[Calendar].[Calendar Year], [Date].[Calendar].DefaultMember) ) ON 0FROM [Sales Summary]; |
| How do you write MDX query that uses execution date/time as a parameter? SELECT {[Measures].[Internet Order Count]} ON 0, {StrToMember("[Date].[Date].[" + Format(now(), "MMMM dd, yyyy") + "]")} ON 1FROM [Direct Sales]; | |
| Need MDX Query to get latest months and previous years same months data SELECT {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]; | I need an MDX query to show year level data for all years except the last one, and month level data for the last year. SELECT {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]; |
| How to create calculated member for AVG sales over last 3 years based on NOW()? CREATE 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]) ; | How do I calculate sales for 12 Month to date in MDX? WITH 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] |
| How can I get Last (Previous) Year to Date (YTD) values?WITH MEMBER [Measures].[Current YTD] ASSUM(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 0FROM [Adventure Works]WHERE ([Date].[Calendar].[Date].[March 22, 2004]) | MDX query to get count of months with sales amount > 0 in defined period WITH 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 1FROM [Adventure Works]WHERE ([Date].[Calendar].[Calendar Year].&[2003]: [Date].[Calendar].[Calendar Year].&[2004]); |
| How do you calculate monthly average of a year? WITH MEMBER [Measures].[AvgVal] AS Avg( Descendants([Date].[Calendar].[Calendar Year].&[2004], [Date].[Calendar].[Month]), [Measures].[Internet Order Count] )SELECT {[Measures].[AvgVal]} ON 0FROM [Adventure Works]WHERE ([Product].[Product Model Lines].[Model Name].&[Classic Vest]) | How do you calculate monthly average of a year including empty months? WITH MEMBER [Measures].[AvgVal] AS Avg( Descendants([Date].[Calendar].[Calendar Year].&[2004], [Date].[Calendar].[Month]), CoalesceEmpty([Measures].[Internet Order Count], 0))SELECT {[Measures].[AvgVal]} ON 0FROM [Adventure Works]WHERE ([Product].[Product Model Lines].[Model Name].&[Classic Vest]) |
Please contribute to this document. You can send e-mail with your query or just leave here comment. We will add query to the list.
Tags: mdx, time dimension