Finding Last n members of a Particular month of a particular Year from a Time Dimension using MDX



Suppose we have a Time Dimension where we have four attributes:

Year, Month, Day and PeriodKey

 

Now we create a hierarchy using these attributes. Creating a Time dimension hierarchy is a little bit tricky. The following link shows a detailed method of how we can create a Time Dimension and hierarchy.

Period Dimension (Time Dimension) Creation with Year, Month, Day Hierarchy

 

 

Now suppose we want to create a report using the last 7 days of November 2009. Let’s create a MDX to have the desired data. We will see the MDX query step by step.

First we are just using a dummy measure using the WITH MEMBER systax.

WITH MEMBER [MEASURES].[TEMP]

AS

1

 

First we take all the Day level member that is under the year 2009

WITH MEMBER [MEASURES].[TEMP]

AS

1

select {

    descendants([Period].[Hierarchy].[Year].&[2009], [Period].[Hierarchy].[Day])

} on rows,

{[MEASURES].[TEMP] } on columns

from [Test]

 

So we will get all the days of year 2009. But we need the days of a particular year. So now we filter these days to take the days of only November. So how do we do this? We can take every member of the above set and check whether its ancestor at Month level is November or not. So the MDX looks like

 

WITH MEMBER [MEASURES].[TEMP]

AS

1

select {

    filter(

        descendants([Period].[Hierarchy].[Year].&[2009], [Period].[Hierarchy].[Day]),

           ancestor([Period].[Hierarchy].currentmember,[Period].[Hierarchy].[Month]) is   [Period].[Hierarchy].[Month].&[11]&[2009]

)

} on rows,

{[MEASURES].[TEMP] } on columns

from [Test]

 

Now we have only the days in November 2009.

To get the last 7 days of this month, we can now simply use the TAIL() function.

 

WITH MEMBER [MEASURES].[TEMP]

AS

1

select {

    tail(

       filter(

       descendants([Period].[Hierarchy].[Year].&[2009], [Period].[Hierarchy].[Day]),

       ancestor([Period].[Hierarchy].currentmember,[Period].[Hierarchy].[Month]) is    [Period].[Hierarchy].[Month].&[11]&[2009]

),

7)} on rows,

{[MEASURES].[TEMP] } on columns

from [Test]

Pretty easy isn’t it?

 

Aggregated value of last n days of every month of a year of a Time Dimension using MDX

 

Continuing from the above, we can also create a report that shows the aggregated value of last 7 days of every month of a year. This is even simpler than the previous one. We can first take all the DESCENDANTS at Day level and then take the last 7 members using the TAIL() function.

 

 

WITH MEMBER [MEASURES].[TEMP]

AS

sum(tail

           (

              descendants(

              [Period].[Hierarchy].currentmember,

              [Period].[Hierarchy].[Day]

           ),

           7

       ),

       1

)

 

select { [Period].[Hierarchy].[Month].members } on rows,

{[MEASURES].[TEMP] } on columns

from [Test]

This MDX will give all the months of all the members. If we want the months of only a particular year, we can use a filter expression in the select on rows.

WITH MEMBER [MEASURES].[TEMP]

AS

sum(tail

           (

                 descendants(

                           [Period].[Hierarchy].currentmember,

                           [Period].[Hierarchy].[Day]

                 ),

                 7

            ),

      1

)

 

select {

Filter(

      [Period].[Hierarchy].[Month].members,

ancestor([Period].[Hierarchy].CurrentMember, [Period].[Hierarchy].[Year]) is [Period].[Hierarchy].[Year].&[2009]

)} on rows,

{[MEASURES].[TEMP] } on columns

from [Test]

 

In fact,we can play around with these and create any reports that we want.