“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]

“Distinctcount” in DAX and MDX

If you want to count distinct values in DAX you have at least two ways of doing this. You can use the distinctcount or you can use countrows with distinct.

A sample can be viewed below

EVALUATE
SUMMARIZE (
    'Product',
    "Number of products version 1", DISTINCTCOUNT ( 'Product'[Product Id] ),
    "Number of products version 2", COUNTROWS ( DISTINCT ( 'Product'[Product Id] ) )
)

In MDX the same thing can be done using this code

WITH MEMBER CountDistinct
AS COUNT(DISTINCT [Product].[Product Id].[Product Id])

SELECT {[Measures].[CountDistinct]} ON 0
FROM [Internet Operation]

You also have a distinctcount in MDX that can be used.

Add a filter to your query using the “Filter” function

It is really easy to add filters to your DAX query. In this post I will show you some samples.

Lets take a look at the following query:

EVALUATE
FILTER (
SUMMARIZE (
'Internet Sales',
'Date'[Calendar Year],
'Date'[Month Name],
"Sales", 'Internet Sales'[Internet Total Sales]
),
'Date'[Calendar Year] = 2007
)

This will give you sales per year per month for year 2007.

The MDX for the same resultset might look like this (you can of course move the year filter to the where clause)

SELECT [Measures].[Internet Total Sales] on 0,
[Date].[Calendar Year].&[2007] * [Date].[Month Name].[Month Name] on 1
from [Internet Operation]

In the next query I have added another filter

EVALUATE
(
FILTER(
SUMMARIZE
(
'Internet Sales',
'Date'[Calendar Year],
'Date'[Month Name],
"Sales", 'Internet Sales'[Internet Total Sales]
), 'Date'[Calendar Year] = 2007 && 'Internet Sales'[Internet Total Sales] > 500000
)
)

This new condition will remove all rows with sales less than 500000 for year 2007

The equal query in MDX will look like

SELECT [Measures].[Internet Total Sales] on 0,
FILTER([Date].[Calendar Year].&[2007] * [Date].[Month Name].[Month Name],[Measures].[Internet Total Sales] > 500000) on 1
from [Internet Operation]

The next sample will be a little more complex. It will give you sales per year per month compared to the years total. I am not sure if this is the most easy way of doing this. Or the best way 🙂

DEFINE
MEASURE 'Internet Sales'[Sum Year] =
CALCULATE (
'Internet Sales'[Internet Total Sales],
ALLSELECTED ()
)
MEASURE 'Internet Sales'[Percent of year] =
FORMAT (
CALCULATE (
'Internet Sales'[Internet Total Sales]
/ 'Internet Sales'[Sum Year]
),
"Percent"
)
EVALUATE
ADDCOLUMNS (
FILTER (
SUMMARIZE (
'Internet Sales',
'Date'[Calendar Year],
'Date'[Month Name],
"Sales", 'Internet Sales'[Internet Total Sales]
),
'Date'[Calendar Year] = 2007
),
"Year", 'Internet Sales'[Sum Year],
"Percent of year", 'Internet Sales'[Percent of year]
)

The get a similar resultset in MDX we can use this query

WITH MEMBER [Measures].[Sum Year] AS
( Root ( [Date].[Calendar Year] ),
[Measures].[Internet Total Sales] )
MEMBER [Measures].[Percent of year] AS
( [Measures].[Internet Total Sales] / [Measures].[Sum Year] ),
FORMAT_STRING = 'Percent'
SELECT
{ [Measures].[Internet Total Sales], [Measures].[Sum Year], [Measures].[Percent of year] } ON 0,
[Date].[Calendar].[Year].&[2007] * [Date].[Month Name].Children ON 1
FROM [Internet Operation]