locked
CURRENT YEAR PREVIOUS TWO MONTHS SALES+MDX RRS feed

  • Question

  • QUERY TO GET CURRENT YEAR LAST TWO MONTH SALES DYNAMICALLY IN MDX
    Thursday, January 30, 2014 10:27 AM

Answers

  • assuming you are looking for data with the latest two months and taking adventure works DW 2008  as sample DB

    try this

    select [Measures].[Internet Sales Amount] on 0,

     {Tail(nonempty(descendants([Date].[Calendar].[Calendar Year],[Date].[Month of Year]))).item(0),

     Tail(nonempty(descendants([Date].[Calendar].[Calendar Year],[Date].[Month of Year]))).item(0).prevmember}  on 1

     from [Adventure Works]

    • Proposed as answer by Elvis Long Thursday, February 6, 2014 8:19 AM
    • Marked as answer by Elvis Long Monday, February 10, 2014 9:59 AM
    Thursday, January 30, 2014 12:47 PM
  • Hello,

    We can use Lag() function to achieve this requirement, please refer to the following similar thread about this topic:
    SSAS 2008 - MDX Dynamic Data Set for last 12 months? http://social.technet.microsoft.com/Forums/sqlserver/en-US/f0e50fc6-8197-4251-8280-bc2989fe4060/ssas-2008-mdx-dynamic-data-set-for-last-12-months?forum=sqlanalysisservices

    If this isn't your case, please elaborate your requirement with more detail.

    Regards,


    Elvis Long
    TechNet Community Support

    • Marked as answer by Elvis Long Monday, February 10, 2014 9:59 AM
    Thursday, February 6, 2014 8:44 AM

All replies

  • assuming you are looking for data with the latest two months and taking adventure works DW 2008  as sample DB

    try this

    select [Measures].[Internet Sales Amount] on 0,

     {Tail(nonempty(descendants([Date].[Calendar].[Calendar Year],[Date].[Month of Year]))).item(0),

     Tail(nonempty(descendants([Date].[Calendar].[Calendar Year],[Date].[Month of Year]))).item(0).prevmember}  on 1

     from [Adventure Works]

    • Proposed as answer by Elvis Long Thursday, February 6, 2014 8:19 AM
    • Marked as answer by Elvis Long Monday, February 10, 2014 9:59 AM
    Thursday, January 30, 2014 12:47 PM
  • Hi,

    What do you mean by Current year's last month ...Is it 2014 Jan and Feb or what...Elaborate your requirement

    so team here can help you in more correct way.


    Aniruddha http://aniruddhathengadi.blogspot.com/

    Saturday, February 1, 2014 2:40 AM
  • Hello,

    We can use Lag() function to achieve this requirement, please refer to the following similar thread about this topic:
    SSAS 2008 - MDX Dynamic Data Set for last 12 months? http://social.technet.microsoft.com/Forums/sqlserver/en-US/f0e50fc6-8197-4251-8280-bc2989fe4060/ssas-2008-mdx-dynamic-data-set-for-last-12-months?forum=sqlanalysisservices

    If this isn't your case, please elaborate your requirement with more detail.

    Regards,


    Elvis Long
    TechNet Community Support

    • Marked as answer by Elvis Long Monday, February 10, 2014 9:59 AM
    Thursday, February 6, 2014 8:44 AM