Home > MDX > MDX – Date calculations

MDX – Date calculations

It is very common to work with Dates while developing reports on SSAS cubes using MDX. Here presenting three basic and mostly used Date calculations.
Step 1: YTD
Year To Date calculation gives the aggregated/sum of the measures from the Year to the date of the MDX so the scope of the aggregation is the year
Step 2: MTD
Month To Date calculation gives the aggregated/sum of the measures from the Month to the date of the MDX so the scope of the aggregation is the month
Step 3: PeriodsToDate
PeriodsToDate is the more generalised calculation of YTD and MTD, it gives the aggregated/sum values with in the specified range of dates
Here I prepared one query to accommodate all the above three functions as
 

WITH MEMBER [Measures].[YTDSales Amount] AS 
sum(YTD([Date].[Calendar].currentmember)
, [Measures].[Sales Amount])
 MEMBER [Measures].[MTDSales Amount] AS 
sum(MTD([Date].[Calendar].currentmember)
, [Measures].[Sales Amount])
 MEMBER [Measures].[PTDSales Amount] AS 
sum(PeriodsToDate([Date].[Calendar].[Calendar Year],[Date].[Calendar].currentmember)
, [Measures].[Sales Amount])

SELECT {[Measures].[Sales Amount],[Measures].[MTDSales Amount]
,[Measures].[YTDSales Amount]
,[Measures].[PTDSales Amount]} ON COLUMNS
,
{[Date].[Calendar].[Date].members} ON ROWS
FROM 
( SELECT [Date].[Calendar].[Calendar Year].&[2007] ON 0 FROM 
[Adventure Works]
)

 


 
Observations:
 
1. The values are going the running sums’ with dates with same values at the starting of the year i.e. 1 January 2007
2. The MTD value has changed to accommodate the next month values i.e. 1 February 2007
 
Points to consider:
 
1. The YTD() and MTD() functions also works without the members as it will take default year member
2. Here presented for only one year to narrow-down the result set

Advertisements
Categories: MDX Tags:
  1. No comments yet.
  1. No trackbacks yet.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: