Running total in MDX

Here is a sample on how you can get running total in MDX. The query below gives sales and running total for january 2011 per date.

WITH MEMBER [Measures].[Running Total] AS
  Aggregate (
    { [Date].[Calendar].CurrentMember.Parent.FirstChild : [Date].[Calendar].CurrentMember },
    [Measures].[Internet Sales Amount]
  )
SELECT
{ [Measures].[Internet Sales Amount], [Measures].[Running Total] } ON 0,
[Date].[Calendar].[Month].&[2011]&[1].Children ON 1
FROM [Adventure Works]

And the result will look like this:

Runningtotal MDX 1

A query for month level can look like this:

WITH MEMBER [Measures].[Running Total] AS
  Aggregate (
    { [Date].[Calendar].CurrentMember.Parent.FirstChild : [Date].[Calendar].CurrentMember },
    [Measures].[Internet Sales Amount]
  )
SELECT
{ [Measures].[Internet Sales Amount], [Measures].[Running Total] } ON 0,
[Date].[Calendar].[Calendar Quarter].&[2011]&[1].Children ON 1
FROM [Adventure Works]

This will look like this

Runningtotal MDX 2

Because the parent level of month is quarter you will only get 3 months. In the cubes I make for customers I always have hierarchies for “Year – Month – Date” and then it should not be any problem 🙂

Please note that this sample has to be run against a multidimensional instance of Analysis Services.

Dynamic date filter in MDX using VBA

When I create a new cube I always add a few named sets for getting time periods automatically. These are nice to have if I have scheduled reports that should present data for today, yesterday, last week or maybe current month.

An example could be a named set that returns yesterdays date.

CREATE SET CURRENTCUBE.[Yesterdays date] AS 
StrToMember("[Date].[Date].&[" + Format(now()-1, "yyyy-MM-dd") + "T00:00:00]", CONSTRAINED); 

And the named set might be used like this

SELECT [Measures].[Internet Total Sales] ON 0,
 [Current Date] 
 * [Product].[Model Name].[Model Name] ON 1
FROM [Internet Sales]

You can of course also use this within a single query like this.

WITH SET [Current date] AS 
StrToMember("[Date].[Date].&[" + Format(now()-1, "yyyy-MM-dd") + "T00:00:00]")    -- minus 1 gives yesterdays date

SELECT [Measures].[Internet Total Sales] ON 0,
 [Current Date] 
 * [Product].[Model Name].[Model Name] ON 1
FROM [Internet Sales]

Please note that the format of the member must be correct. In my case the Date dimensions members look like this: [Date].[Date].&[2005-01-10T00:00:00]. If you try to run these samples against the sample database you will not get any result. Since the database does not have data for the current period. If you want to test a valid date you can check how many days you have to remove by running this SQL:

select DATEDIFF(day, getdate(), '2006/05/18')

And then you can change the code “now()-1” to “now()-[The number of days the query returned]”

Here are some other useful samples

//Sample date sets.
CREATE SET CURRENTCUBE.[Current date] AS 
StrToMember("[Date].[Date].&[" + Format(now(), "yyyy-MM-dd") + "T00:00:00]", CONSTRAINED);       
     
-- Must be used with [This Year]
CREATE SET CURRENTCUBE.[This month] AS 
StrToMember("[Date].[Month].&[" + Format(now()-1, "MM") + "]", CONSTRAINED);        

CREATE SET CURRENTCUBE.[This year] AS 
StrToMember("[Date].[Calendar Year].&[" + Format(now(), "yyyy") + "]", CONSTRAINED);      

-- Can you create a named set for current week? Last week?

Top products within each category in MDX

If you want to have top products for each model this is a simple way of doing that in MDX.

SELECT [Measures].[Internet Total Sales] ON COLUMNS,
NON EMPTY Generate (
Order (
{ [Product].[Category].[Model].Members },
[Measures].[Internet Total Sales],
ASC
),
Crossjoin (
[Product].[Category].CurrentMember,
TopCount (
[Product].[Product Name].Children,
2,
( [Measures].[Internet Total Sales] )
)
)
) ON ROWS
FROM [Internet Sales]

The result will look like this

Top products within each category

If you want to have top 2 models and then top 5 products you can write a query like this

WITH SET TopModels AS
TopCount (
{ [Product].[Category].[Model] },
2,
( [Measures].[Internet Total Sales] )
)
SELECT [Measures].[Internet Total Sales] ON COLUMNS,
Generate (
{ TopModels },
Crossjoin (
{ [Product].[Category].CurrentMember },
TopCount (
[Product].[Product Name].Children,
5,
( [Measures].[Internet Total Sales] )
)
)
) ON ROWS
FROM [Internet Sales]

And the result will look like this:

Top products within each category 2

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