How to include all the days of the week in a calculation when week is between two months?

Question

I have an MDX calculation that is working fine except for one case.

It is getting a roll up for a week and it looks like this:

with
  MEMBER [Measures].[WTD]
as AGGREGATE( [Date].[Date].CURRENTMEMBER.Parent.CHILDREN, [Measures].[Survey Count] )
SELECT 
NON EMPTY 
{ 
   [Measures].[Survey Count],
   [Measures].[WTD]
} ON COLUMNS, 
NON EMPTY 
{ 
       ( [Date].[Date].[Day].ALLMEMBERS ) 
} ON ROWS 
FROM [myCube];

By week I mean Sunday to Saturday. This is the way the calculation is needed.

The problem is that when a Sunday to Saturday week has a change in month. So for the last Sunday to Saturday week in March 2017, for example, the second last day is March 31, and the last day is April 1. In the cube this looks like: week 13 has the March 26 - March 31 values (six days), and then week 13 appears again under the month of April. It is the first week under the month of April and that week 13 only has the one day - April 1. Then week 14 is next in April with a full 7 days (April 2 - April 8).

The cube has a Date hierarchy that has under it the Year, Quarter, Month, Week, and Day dimensions.

So how can I modify this MDX to include a full week (all seven days between Sunday to Saturday) when a week is split between two months?

saso saso · Accepted Answer · 2017-08-30T15:17:10

The reason for the behaviour you're seeing is the hierarchy of your Date dimension. E.g. the month level is above than the week level and this means that the .Parent function always returns the days of the week in the month of the .CurrentMember date.

You have different options to solve this:

1)

You can simply use the MDX WTD() function, you only need to correctly configure the Date dimension so that the attributes have correct Type.

2)

You could create another Date hierarhy that would have year/week as 1st level and day as 2nd level, like this:

YearWeek (e.g. Y2017/W51, Y2017/W52, etc.)
Day (e.g. 2017-12-14, 2017-12-15, etc.)

Your existing code will work in this case, you would only need to refer to this new hierarchy.

3)

You could also define Weekday as a property of the Day attribute and then you could create your WTD measure using the existing Date hierarchy like this:

WITH
  MEMBER [Measures].[WTD]
AS
    AGGREGATE(      
        TAIL(FILTER( NULL:[Date].[Day].CurrentMember, [Date].[Day].Properties( "Weekday" )="Sunday" ), 1).Item(0) : [Date].[Day].CurrentMember,
        [Measures].[Survey Count]
    )

How to include all the days of the week in a calculation when week is between two months?

1 Answers