Report Portal

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])
 

PDF Download here.

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

 

2007-2015 VidasSoft Systems Inc.