Retrieving data for current year, current month and current day.

You can retrieve current year, current month and current day by using VBA functions.

MDX for retrieving current Year;

WITH MEMBER [Measures].[Current Year] AS

    VBAMDX.Format(VBAMDX.Now(),"yyyy")

SELECT

  {[Measures].[Current Year]} ON COLUMNS

FROM [Adventure Works];

MDX for retrieving current Month;

WITH MEMBER [Measures].[Current Month] AS

    VBAMDX.Format(VBAMDX.Now(),"MM")

SELECT

  {[Measures].[Current Month]} ON COLUMNS

FROM [Adventure Works];

MDX for retrieving current Date;

WITH MEMBER [Measures].[Current Date] AS

    VBAMDX.Format(VBAMDX.Now(),"dd")

SELECT

  {[Measures].[Current Date]} ON COLUMNS

FROM [Adventure Works];

Sometimes user wants to retrieve data for current year, current month, and current day. You can retrieve data by using VBA function and STRTOMEMBER function.

Check following MDX samples by using Adventure Works sample.

MDX for retrieving current Year;

WITH

  MEMBER [Measures].[Current Year Value] AS

    (

      StrToMember

      (

            "[Date].[Calendar].[Calendar Year].&["

          + VBAMDX.Format(VBAMDX.Now(),"yyyy") + "]"

      )

     ,[Measures].[Internet Sales Amount]

    )

SELECT

  [Measures].[Current Year Value] ON COLUMNS

FROM [Adventure Works];

MDX for retrieving current Month;

WITH

  MEMBER [Measures].[Current Month Value] AS

    (

      StrToMember

        (

            "[Date].[Calendar].[Month].&[" +

             VBAMDX.Format(VBAMDX.Now(),"yyyy") + "]

            &[" + VBAMDX.Format(VBAMDX.Now(),"MM") + "]"

        )

     ,[Measures].[Internet Sales Amount]

    )

SELECT

  [Measures].[Current Month Value] ON COLUMNS

FROM [Adventure Works];

MDX for retrieving current Day;

WITH

  MEMBER [Measures].[Today's Value] AS

    (

      StrToMember

      (

           "[Date].[Calendar].[Date].&[" +

           VBAMDX.Format(VBAMDX.Now(),"yyyyMMdd")

           + "]"

      )

     ,[Measures].[Internet Sales Amount]

    )

SELECT

  [Measures].[Today's Value] ON COLUMNS

FROM [Adventure Works];

Note : AdventureWorks sample does not contain data for current year,so if you execute above mentioned MDX,it results in Null values but if you insert data for current year,it will result the required output.