“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

WITH MEMBER [Year To Date] AS
( 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] ),
FORMAT_STRING = 'Percent'
SELECT
{ [Measures].[Year To Date], [Measures].[Last Year To Date], [Measures].[Change] } ON 0,
[Date].[Calendar].[Date].& ON 1

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.

“Year To Date” in DAX and MDX

To get the sales for “year to date” you can use the TotalYTD function in DAX.

Below is a simple example on how this can be done.

EVALUATE
FILTER (
SUMMARIZE (
'Date',
'Date'[Date],
"Sales Year To Date", TOTALYTD (
'Internet Sales'[Internet Total Sales],
'Date'[Date]
),
"Sales Quarter To Date", TOTALQTD (
'Internet Sales'[Internet Total Sales],
'Date'[Date]
),
"Sales Month To Date", TOTALMTD (
'Internet Sales'[Internet Total Sales],
'Date'[Date]
)
),
'Date'[Date] = DATE ( 2006, 10, 01 )
)

The equal query in MDX might look like this

WITH MEMBER [Sales Year To Date]
AS
(SUM(YTD([Date].[Calendar].CurrentMember),[Measures].[Internet Sales Amount]))

MEMBER [Sales Month To Date]
AS
(SUM(MTD([Date].[Calendar].CurrentMember),[Measures].[Internet Sales Amount]))

SELECT {
[Measures].[Sales Year To Date],
[Measures].[Sales Month To Date]
} ON 0,
[Date].[Calendar].[Year].&.&.&.&[10 - October].& ON 1
FROM [Adventure Works Internet Sales Model]

Please note that this query will give an error message if you run it against the AdventureWorks database in Tabular mode. This is because the year level is not defined as it should.

You will most likely get this error:

“Executing the query …
Query (3, 7) By default, a year level was expected. No such level was found in the cube.
Execution complete”

If you are running Analysis Services in multidimensional this query will work

WITH MEMBER [Sales Year To Date]
AS
(SUM(YTD([Date].[Calendar].CurrentMember),[Measures].[Internet Sales Amount]))

MEMBER [Sales Month To Date]
AS
(SUM(MTD([Date].[Calendar].CurrentMember),[Measures].[Internet Sales Amount]))

SELECT {
[Measures].[Sales Year To Date],
[Measures].[Sales Month To Date]
} ON 0,
[Date].[Calendar].[Date].& ON 1