locked
MDX on last 6 month RRS feed

  • Question

  • I want to return list of most recent 6 month, so I need to find out what the current month is, then return list of 6 month back,for example, if current month is Nov, then I want to return Jun,Jul,Aug,Sept,Oct,Nov, so I try the following test using AdventureWorks:

    select

     

    {} on 0,

    ([Date].[Month of Year].

     

    currentmember.lag(5): [Date].[Month of Year].currentmember) on 1

    from

     

    [Adventure Works]

    But it returns All Periods, instead of last 6 month, anything I miss?

    Thanks


    Don

    Wednesday, November 11, 2009 5:25 AM

Answers

  • Note that .currentmember will not automatically return the current month, unless the default member has been somehow set to it. There's a sample Adventure Works query below which works - you can refer to this blog entry for discussion of how to set the current date:

    How to get the today's date in MDX

    With
    Set [CurrentMonth] as
    {[Date].[Calendar].[Month].&[2003]&[11]}
    select{} on 0, 
    LastPeriods(6, [CurrentMonth].item(0)) on 1
    from
    [Adventure Works]

     

     

     


    - Deepak
    • Proposed as answer by Raymond-Lee Friday, November 13, 2009 6:11 AM
    • Marked as answer by Raymond-Lee Friday, November 20, 2009 9:18 AM
    Wednesday, November 11, 2009 7:12 AM

All replies

  • I want to create mdx expression like "Running sales amount by most recent 3 month", so if it is now November, then the running total by most recent 3 month would be:
              Amount
    Sept    21211
    Oct     12121
    Nov     12122

    What is the mdx function to do that? I know there is Lag function, but it seems Lag (3) of current member will give you Oct, Nov and Dec, which is not running 3 month starting from Nov.


    Don
    • Merged by Raymond-Lee Friday, November 20, 2009 9:17 AM the same topic
    Tuesday, November 10, 2009 6:05 AM
  • Hi,

    Try this...


    WITH

    SET [Last 3 Months] AS
           {Ancestor(Time.Time.CurrentMember,[Time].[Time].Month).Lag(2)
           :Ancestor(Time.Time.CurrentMember,[Time].[Time].Month)}


    SELECT
    {[Last 3 Months]} on columns

     

    from [Cube Name]

    Regards
     Gokul

    Tuesday, November 10, 2009 6:26 AM
  • Using  LastPeriods works perfectly in your case. Here is the complete MDX that I got it working in Adventure Works 2008 database.

    WITH
      MEMBER [Measures].[Last3Months] AS
        Sum
        (
          {
            LastPeriods
            (3
             ,[Date].[Fiscal].CurrentMember
            )
          }
         ,[Measures].[Amount]
        )
    SELECT
      {
        [Measures].[Last3Months]
       ,[Measures].[Amount]
      } ON COLUMNS
     ,Descendants
      (
        [Date].[Fiscal].[Fiscal Year].&[2004]
       ,[Date].[Fiscal].[Month]
       ,self
      ) ON ROWS
    FROM [Adventure Works];


    Thanks,
    Ashok
    Tuesday, November 10, 2009 10:45 AM
  • Note that .currentmember will not automatically return the current month, unless the default member has been somehow set to it. There's a sample Adventure Works query below which works - you can refer to this blog entry for discussion of how to set the current date:

    How to get the today's date in MDX

    With
    Set [CurrentMonth] as
    {[Date].[Calendar].[Month].&[2003]&[11]}
    select{} on 0, 
    LastPeriods(6, [CurrentMonth].item(0)) on 1
    from
    [Adventure Works]

     

     

     


    - Deepak
    • Proposed as answer by Raymond-Lee Friday, November 13, 2009 6:11 AM
    • Marked as answer by Raymond-Lee Friday, November 20, 2009 9:18 AM
    Wednesday, November 11, 2009 7:12 AM
  • Hi

    Definetely you got your result set, but i prefer that right here this link more helpfull to others and also for those who are new with MDX.

    Please check the below link in which date filtering describes with different taste for reducing the updating time, and how beautifully describe about the AS2008 boclk system.

    http://sqlblog.com/blogs/mosha/archive/2008/10/22/optimizing-mdx-aggregation-functions.aspx

    Thanks
    M Ahsan

    Tuesday, April 2, 2013 5:34 AM