Wednesday, July 25, 2012

MDX for getting data for last 7 or 15 days

This is a very common requirement in most of organizations where they want to analyze the sum of last 7 or 15 days data and if you are asked to write MDX for such requirements then you can write your MDX in the following way;

MDX for getting the SUM of last 7 days.


WITH
  MEMBER [Measures].[Sum Of Last 7 Days] AS
    Sum
    (
      {
          [Date].[Calendar].CurrentMember.Lag(6)
        :
          [Date].[Calendar].CurrentMember
      }
     ,[Measures].[Internet Sales Amount]
    )
SELECT
  {
    [Measures].[Internet Sales Amount]
   ,[Measures].[Sum Of Last 7 Days]
  } ON COLUMNS
FROM [Adventure Works]
WHERE
  [Date].[Calendar].[Date].&[20070827];

MDX for getting the SUM of last 15 days.

WITH
  MEMBER [Measures].[Sum Of Last 15 Days] AS
    Sum
    (
      {
          [Date].[Calendar].CurrentMember.Lag(14)
        :
          [Date].[Calendar].CurrentMember
      }
     ,[Measures].[Internet Sales Amount]
    )
SELECT
  {
    [Measures].[Internet Sales Amount]
   ,[Measures].[Sum Of Last 15 Days]
  } ON COLUMNS
FROM [Adventure Works]
WHERE
  [Date].[Calendar].[Date].&[20070827];

You can use above MDX for getting the sum of last any number of days by just changing the value of Index in .Lag(Index) function.

2 comments:

  1. Hi Aniruddha,

    In this example you hardcode the date and the lag index into the MDX query. Is there a way of controlling the two by a input paramater?

    Thanks

    //Martin

    ReplyDelete

  2. Wow! Great post! The content is very rich, and I really like it.

    Server 2016
    Microsoft Server 2016 Support

    ReplyDelete