查看源

How do you get Last month in the time dimension?

SELECT 
	ClosingPeriod([Date].[Calendar].[Month], [Date].[Calendar].DefaultMember) ON 0 
FROM 
	[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 0 
FROM 
	[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 0 
FROM 
	[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 0 
FROM 
	[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 0 
FROM 
	[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 1 
FROM 
	[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] 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])

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 1 
FROM 
	[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 0 
FROM 
	[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 0 
FROM 
	[Adventure Works] 
WHERE 
	([Product].[Product Model Lines].[Model Name].&[Classic Vest])