MDX same measure hierarchy on row

Question

i'm new to MDX. I've been strugglin real hard over this one. I'm trying to compare the same measure over 2 different week. I have a Date dimension and a total measure. I want it to be displayed like the following:

              Monday Tuesday Wednesday Thursday Friday Saturday Sunday
First  Week    120    54.21   128.87     1298     121    213     176
Second Week    98     67.21   62.32      1276     432    98      127

My query so far looks like this:

    WITH
    MEMBER [Measures].[FirstWeek]
    AS ([Measures].[Total], {[Dim Date].[Date ID].&[20160429]:[Dim Date].[Date ID].&[20160504]})
    MEMBER [Measures].[SecondWeek]
    AS ([Measures].[Total], {[Dim Date].[Date ID].&[20160505]:[Dim Date].[Date ID].&[20160512]})

SELECT
    {[Dim Date].[Week Day].[Week Day]} ON COLUMNS,
    ([Measures].[FirstWeek] , [Measures].[SecondWeek]) ON ROWS
FROM [Example]
WHERE (...)

The equivalent in SQL is really simple but somehow in MDX it won't let me use twice the same measure even if it's based on different dimension time. Any hint would be really appreciated !

Answer 1

This is wrong:

([Measures].[Total], {[Dim Date].[Date ID].&[20160429]:[Dim Date].[Date ID].&[20160504]})

You're using a set plus a measure with no aggregation. Read my article for more details. Try:

Aggregate({[Dim Date].[Date ID].&[20160429]:[Dim Date].[Date ID].&[20160504]},[Measures].[Total])