“Year to date” and “Last Year To Date” in MDX

To get “Year To Date” and “Last Year To Date” in MDX we can use the YTD function. To get “Last Year To Date” we also have to use the “ParallelPeriod” function. The “ParallelPeriod” gives us the same dates for an other period. In this case we switch back one year.

Here is a sample

( Sum (
Ytd ( [Date].[Calendar].CurrentMember ),
[Measures].[Internet Sales Amount]
) )
MEMBER [Last Year To Date] AS
( Sum (
Ytd (
ParallelPeriod ( [Date].[Calendar].[Calendar Year], 1, [Date].[Calendar].CurrentMember )
[Measures].[Internet Sales Amount]
) )
MEMBER [Change] AS
divide ( [Measures].[Year To Date], [Last Year To Date] ),
{ [Measures].[Year To Date], [Measures].[Last Year To Date], [Measures].[Change] } ON 0,
[Date].[Calendar].[Date].&[20120214] ON 1
FROM [Adventure Works]

And here is the result


This code will work in Analysis Services Multidimensional. If you want to do the same for “Month to date”, “Quarter to date” or similar you can change YTD to MTD or QTD. If you plan to use week you should use an other hierarchy and then change to WTD.

I would recommend you to add this functionality as  calculations in the cube. There are several great scripts out there for you to copy. Such as DateTool by Marco Russo.