“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].&[2006].&[2].&[4].&[10 - October].&[10] 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].&[20110214] ON 1
FROM [Adventure Works]
Advertisements