Set of Days in past 6 equivalent MTDs

Question

Imagine today it is the 22nd June 2010 (I've used this date as AdvWrks cube is old)

I would like to find the set of days that make up this month i.e. 1st June - 22nd June plus the days in the previous 5 equivalent months to date.

So these days would make up the set:

(1st Jan 2010 - 22nd Jan 2010) +
(1st Feb 2010 - 22nd Feb 2010) +
(1st Mar 2010 - 22nd Mar 2010) +
(1st Apr 2010 - 22nd Apr 2010) +
(1st May 2010 - 22nd May 2010) +
(1st Jun 2010 - 22nd Jun 2010)

The following gives me this set of 132 days:

WITH 
  SET [Days in Current Month] AS 
     [Date].[Calendar].[Date].&[20100601]:[Date].[Calendar].[Date].&[20100622]
  SET [Mths in Past 6 Mths] AS 
    Tail
    (
      [Date].[Calendar].[Month].MEMBERS
     ,6
    ) 
  SET [Prev Equiv MTDs] AS 
    Generate
    (
      [Mths in Past 6 Mths]
     ,Head
      (
        Descendants
        (
          [Mths in Past 6 Mths].CurrentMember
         ,[Date].[Calendar].[Date]
         ,SELF
        )
       ,[Days in Current Month].Count
      )
    ) 
SELECT 
  {} ON 0
 ,[Prev Equiv MTDs] ON 1
FROM [Adventure Works];

Is it possible to find this set of days without using the Generate function?

SouravA SouravA · Accepted Answer · 2015-06-24T03:59:57

Here is one way:

WITH SET FirstDateOfMonth AS [Date].[Calendar].[Date].&[20100622].Parent.FirstChild
SET LastDayOfMonth AS [Date].[Calendar].[Date].&[20100622]


SELECT {} ON 0,
//CurrentMonth
    {   
        FirstDateOfMonth.ITEM(0)
        :
        LastDayOfMonth.ITEM(0)
    }

+
//-1 month
    {
        ParallelPeriod(
                        [Date].[Calendar].[Month],
                        1,
                        FirstDateOfMonth.ITEM(0)
                      )
         :

        ParallelPeriod(
                        [Date].[Calendar].[Month],
                        1,
                        LastDayOfMonth.ITEM(0)
                      )

    }   
+
//-2 month
    {
        ParallelPeriod(
                        [Date].[Calendar].[Month],
                        2,
                        FirstDateOfMonth.ITEM(0)
                      )
         :

        ParallelPeriod(
                        [Date].[Calendar].[Month],
                        2,
                        LastDayOfMonth.ITEM(0)
                      )

    }   
+
//-3 month
    {
        ParallelPeriod(
                        [Date].[Calendar].[Month],
                        3,
                        FirstDateOfMonth.ITEM(0)
                      )
         :

        ParallelPeriod(
                        [Date].[Calendar].[Month],
                        3,
                        LastDayOfMonth.ITEM(0)
                      )

    }   
+
//-4 month
    {
        ParallelPeriod(
                        [Date].[Calendar].[Month],
                        4,
                        FirstDateOfMonth.ITEM(0)
                      )
         :

        ParallelPeriod(
                        [Date].[Calendar].[Month],
                        4,
                        LastDayOfMonth.ITEM(0)
                      )

    }   

+
//-5 month
    {
        ParallelPeriod(
                        [Date].[Calendar].[Month],
                        5,
                        FirstDateOfMonth.ITEM(0)
                      )
         :

        ParallelPeriod(
                        [Date].[Calendar].[Month],
                        5,
                        LastDayOfMonth.ITEM(0)
                      )

    }   

 ON 1
FROM [Adventure Works]

And here is one more:

SELECT 
    {
        [Date].[Calendar].[Date].&[20100622].PARENT.FirstChild
        :
        COUSIN
        (
        [Date].[Calendar].[Date].&[20100622], 
        [Date].[Calendar].[Date].&[20100622].PARENT
        )

    }
    +
    {
        [Date].[Calendar].[Date].&[20100622].PARENT.LAG(1).FirstChild
        :
        COUSIN
              (
                [Date].[Calendar].[Date].&[20100622], 
                [Date].[Calendar].[Date].&[20100622].PARENT.LAG(1)
              )
    }
    +
    {
        [Date].[Calendar].[Date].&[20100622].PARENT.LAG(2).FirstChild
        :
        COUSIN
              (
                [Date].[Calendar].[Date].&[20100622], 
                [Date].[Calendar].[Date].&[20100622].PARENT.LAG(2)
              )
    }
    +
    {
        [Date].[Calendar].[Date].&[20100622].PARENT.LAG(3).FirstChild
        :
        COUSIN
              (
                [Date].[Calendar].[Date].&[20100622], 
                [Date].[Calendar].[Date].&[20100622].PARENT.LAG(3)
              )
    }
    +
    {
        [Date].[Calendar].[Date].&[20100622].PARENT.LAG(4).FirstChild
        :
        COUSIN
              (
                [Date].[Calendar].[Date].&[20100622], 
                [Date].[Calendar].[Date].&[20100622].PARENT.LAG(4)
              )
    }
    +
    {
        [Date].[Calendar].[Date].&[20100622].PARENT.LAG(5).FirstChild
        :
        COUSIN
              (
                [Date].[Calendar].[Date].&[20100622], 
                [Date].[Calendar].[Date].&[20100622].PARENT.LAG(5)
              )
    }
    ON 1,
{} ON 0
FROM [Adventure Works]

And one more...

WITH SET FirstDateOfMonth AS [Date].[Calendar].[Date].&[20100622].Parent.FirstChild
SET LastDayOfMonth AS [Date].[Calendar].[Date].&[20100622]
MEMBER Measures.PositionOfDate AS {FirstDateOfMonth.ITEM(0):LastDayOfMonth.ITEM(0)}.COUNT

SET Dates AS 
    HEAD(
        [Date].[Calendar].[Date].&[20100622].Parent.ITEM(0).CHILDREN,
        Measures.PositionOfDate
        )
        +
    HEAD(
        [Date].[Calendar].[Date].&[20100622].Parent.LAG(1).ITEM(0).CHILDREN,
        Measures.PositionOfDate
        )
        +
    HEAD(
        [Date].[Calendar].[Date].&[20100622].Parent.LAG(2).ITEM(0).CHILDREN,
        Measures.PositionOfDate
        )
        +
    HEAD(
        [Date].[Calendar].[Date].&[20100622].Parent.LAG(3).ITEM(0).CHILDREN,
        Measures.PositionOfDate
        )
        +
    HEAD(
        [Date].[Calendar].[Date].&[20100622].Parent.LAG(4).ITEM(0).CHILDREN,
        Measures.PositionOfDate
        )
        +
    HEAD(
        [Date].[Calendar].[Date].&[20100622].Parent.LAG(5).ITEM(0).CHILDREN,
        Measures.PositionOfDate
        )


SELECT Dates ON 0,
{} ON 1
FROM [Adventure Works]

And this way too:

WITH SET FirstDateOfSelectedMonth AS [Date].[Calendar].[Date].&[20100622].Parent.FirstChild
SET SelectedDate AS [Date].[Calendar].[Date].&[20100622]
MEMBER Measures.PositionOfDate AS {FirstDateOfSelectedMonth.ITEM(0):SelectedDate.ITEM(0)}.COUNT

SELECT

        PeriodsToDate
                    (
                        [Date].[Calendar].[Month],
                        SelectedDate.ITEM(0).PARENT.lag(5).FirstChild.LEAD(Measures.PositionOfDate - 1)
                    )  
                    +
        PeriodsToDate
                    (
                        [Date].[Calendar].[Month],
                        SelectedDate.ITEM(0).PARENT.lag(4).FirstChild.LEAD(Measures.PositionOfDate - 1)
                    )  
                    +
        PeriodsToDate
                    (
                        [Date].[Calendar].[Month],
                        SelectedDate.ITEM(0).PARENT.lag(3).FirstChild.LEAD(Measures.PositionOfDate - 1)
                    )  
                    +
        PeriodsToDate
                    (
                        [Date].[Calendar].[Month],
                        SelectedDate.ITEM(0).PARENT.lag(2).FirstChild.LEAD(Measures.PositionOfDate - 1)
                    )  
                    +
        PeriodsToDate
                    (
                        [Date].[Calendar].[Month],
                        SelectedDate.ITEM(0).PARENT.lag(1).FirstChild.LEAD(Measures.PositionOfDate - 1)
                    )  
                    +
        PeriodsToDate
                    (
                        [Date].[Calendar].[Month],
                        SelectedDate.ITEM(0)
                    ) 

ON 1,
{} ON 0
FROM [Adventure Works]

EDIT

Slow code with non-repeating blocks:

WITH SET FirstDateOfSelectedMonth AS [Date].[Calendar].[Date].&[20100601].Parent.FirstChild
SET SelectedDate AS [Date].[Calendar].[Date].&[20100601]
MEMBER Measures.PositionOfDate AS {FirstDateOfSelectedMonth.ITEM(0):SelectedDate.ITEM(0)}.COUNT
MEMBER Measures.PositionOfCurrentDate AS 
([Date].[Calendar].currentmember.Parent.FirstChild:[Date].[Calendar].currentmember).COUNT

SELECT 
    {
    [Date].[Calendar].[Date].&[20100601].Parent.LAG(5).FirstChild.ITEM(0)
    :
    [Date].[Calendar].[Date].&[20100601]
    }
    HAVING Measures.PositionOfCurrentDate <= Measures.PositionOfDate
ON 1,
{} ON 0
FROM [Adventure Works]

Set of Days in past 6 equivalent MTDs

1 Answers