Some of the important time related functions are available in this crb sheet. Query refers to [Adventure Works] cube that comes along with SSAS installation.

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

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]

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

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

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

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

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

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

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

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