Using a date range in calculated member in MDX query for Tabular cube model

Question

I have a requirement where I have to get the values of different measure with each measure having its own date range, the following query works and gets me the data over an year,

WITH  MEMBER [Measures].[Ex1] AS ([Measures].[Average Spending], [Date].[Year].&[2020])

MEMBER [Measures].[Ex2] AS ([Measures].[Average Time Spent], [Date].[Year].&[2019])

SELECT {[Customer].[Name].[Name]} ON 1, {[Measures].[Ex1],[Measures].[Ex2]} ON 0 FROM [Model];

but when I try to pass a date range instead of year, I'm getting an error,

WITH  MEMBER [Measures].[Ex1] AS ([Measures].[Average Spending], [Date].[Year].&[2020])

MEMBER [Measures].[Ex2] AS ([Measures].[Average Time Spent], [Date].[Date].[01-May-2020]:[Date].[Date].[31-May-2020])

SELECT {[Customer].[Name].[Name]} ON 1, {[Measures].[Ex1],[Measures].[Ex2]} ON 0 FROM [Model];

I'm getting the following error,

Executing the query ... The function expects a string or numeric expression for the argument. A tuple set expression was used. Run complete

How do I get the measure for a date range for each calculated member?

FrankPl FrankPl · Accepted Answer · 2021-05-22T08:51:45

If you do not use a tuple (i. e. a combination of single members of the hierarchies like Measures or Year), but a set, then you need to tell MDX what to do with the set elements to get to a single value. I would assume the following will give what you want:

WITH
MEMBER [Measures].[Ex2] AS 
       Aggregate([Date].[Date].[01-May-2020]:[Date].[Date].[31-May-2020],
                 [Measures].[Average Time Spent]
                )
...

Instead of Aggregate, you could also use Sum, Avg, Variance etc. but normally, I prefer using Aggregate, as this uses whichever aggregation method is defined for the measure in the cube, i. e. it is more universal, and the query does not need to be adapted to changes in the cube calculation scripts.

See also Aggregate in the SSAS documentation

Using a date range in calculated member in MDX query for Tabular cube model

1 Answers