Time MDX Cheat Sheets
User Rating: / 37
PoorBest 
Written by Group effort   
Thursday, 22 May 2008 02:59

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.

 
 
Comments (11)
11 Wednesday, 05 December 2012 19:24
Articulus
How do we calculate Prior FY Year first 6 months revenue when we pass getdate() or todays date in where filter in MDX
10 Monday, 24 October 2011 17:24
Dave Brown
I found my solution for calculating rolling count of weeks YTD:
[Measures].[Weeks YTD] as 'Sum((PeriodsToDate([TimePeriod].[Year]),[measures].[count])'
Dimension [TimePeriod] hierarchy *Year - **Week and [Measures].[count] is a distinct count of the SQL table date field used to make the Period dimension in the cube. Measures are crossjoined on rows where [Period].[Week] is on columns shows an aggregate count of weeks as time goes by then this measure can be used to calculate avg sales to date (date stored in the data not today's date) take the sales measure divide by [Weeks YTD].
9 Monday, 24 October 2011 14:44
Dave Brown
I need a count of a time dimension stored as weeks (leveled up to Years) based on a SQL db table date as every Friday so that when you apply this measure to the same time dimension you should see it increment from the start of a year giving you the rolling # of weeks YTD. I can get the total based on MAX for the year but I need the measure to change and show it based on dimension date. When I attempt this I get a duplicate dimension error. (embarrassed to say I'm using SQL Server 2000.) Any suggestions or help would be greatly appreciated.
8 Thursday, 21 July 2011 10:09
GSSV
I have a measure with aggregation as DistinctCount and a Date dimension with a hierarchy - Year->Quarter->Month->Date
For any selected Date Level, we need to get the distinct count of the last date of the level.
Say viewing the results at Year, it should consider only those of the Year last date and perform a distinct count
A Month, should get distinct count of the last day .

Please help me out.

Thanks
7 Saturday, 21 May 2011 10:59
Shawn Frost
I know that calculated members should be created using same hierarchy.
But what i don't get is when i tried to create a member as
WITH MEMBER [A].[Name1] AS [B].[Name2] + [B].[Name3]
gives infinite recursion error but
WITH MEMBER [A].[Name1] AS [B].[Name2]
is giving null result
6 Thursday, 19 May 2011 04:14
Rahesh
Please anybody help to write an MDX based on the following condition.

I want to display sales Qty for the current year upto the current Month, with last two months data as weekly basis and the rest as monthly basis. (donot want to hardcode Year and month).
5 Tuesday, 13 April 2010 13:16
Søren
I have 5 warehousetransactions:

2001-01-01 2
2001-06-01 5
2006-02-01 2
2010-03-01 -2

This should give me a warehouse on 7 at the end of march 2010.
I try to use PeriodstoDate and sum, but I cant get this to work, so that I can see warehouse at a given time.

This is what I do:

with Member [Measures].[totalStock] as
sum(PeriodsToDate(),([Measures].[Stock]))
select {[Measures].[Stock], [Measures].[totalStock]} on columns,
nonempty([TIme Dimension].[Month].members) on rows
from [Itemtransaction CUBE]
where [Item Dimension].[Style no].B00000H

Why does it not work??
4 Wednesday, 10 February 2010 22:04
Kavita Chebbi
I need to calculate Inception To Date (ITD) measure, just like the YTD but this one will span over multiple years. My Date dimension has a hierarchy like this..Calendar Year, Calendar Quarter,Calendar Month..and I tried to use the PeriodsToDate function, but its telling me, I used member expression instead of level expression..

Below gives me YTD amount...
Aggregate
(
PeriodsToDate
(
[AC Period].[AC Period Hierarchy].[Calendar Year],
[AC Period].[AC Period Hierarchy].CurrentMember
),
[Measures].[Gross Actual Amount]
)

And I tried..for ITD amount

Aggregate
(
PeriodsToDate
(
[AC Period].[AC Period Hierarchy].[All],
[AC Period].[AC Period Hierarchy].CurrentMember
),
[Measures].[Gross Actual Amount]
)

I appreciate your help in advance..
3 Friday, 19 June 2009 12:42
qutesanju
I want sum of last 13 months
how to do that?
2 Friday, 18 July 2008 14:20
admin
Khotso,

In MDX you would have to use ParallelPeriod function. Example of MDX query is here:
http://www.ssas-info.com/analysis-services-faq/27-mdx/55-mdx-query-to-get-latest-months-and-previous-years-same-months-data-parallelperiod
1 Friday, 18 July 2008 14:11
khotso
How do i do a DateAdd in MDX? eg. If the current selected Month is [April 20008] and I want to return [April 2007], in sql its DataAdd('YY', -1, Year)... How is this done in MDX?

Tx

 

XL Cubed