none
MTD Current Year

    Question

  • I am using this to get MTD current year but it is returning NULL. Any thoughts? Below is how my hierarchy looks like.

    thanks for the help.

    WITH 
      MEMBER [Measures].[Current MTD] AS 
        Sum
        (
          MTD([Date].[Hierarchy].CurrentMember)
         ,[Measures].[Sales]
        )   
    SELECT [Measures].[Current MTD]  ON 0,
     NON EMPTY (
                [Customer].[Master Customer].[Master Customer].MEMBERS, [Styles].[Category].MEMBERS
                    ) ON 1
    FROM [SalesCube]
    WHERE StrToMember("[Date].[Hierarchy].[Month].["+str(year(now()))+"].["+str(month(now()))+"]")
    


    Tuesday, July 30, 2013 1:37 AM

Answers

  • Hi Sam,

    I think, this is similar to your previous requirement. provide your month key.

    1. expand month to members

    2. Drag and drop one key value on SSMS for understanding of underline key

    To my understanding, you should have quarter in your key


    Prav


    • Edited by Prav_SQL Tuesday, July 30, 2013 1:48 AM Updated comments
    • Marked as answer by Sam1980 Wednesday, July 31, 2013 1:42 AM
    Tuesday, July 30, 2013 1:47 AM
  • Hi

    You did StrToMember("[Date].[Hierarchy].[Month].["+str(year(now()))+"].["+str(month(now()))+"]")

    but your key is [Date].[Hierarchy].[Month].&[2014]&[12]  (used & but not . )

    so we need to change the member construction as

    StrToMember("[Date].[Hierarchy].[Month].["+str(year(now()))+"]&["+str(month(now()))+"]")

    Happy to help!


    Prav


    • Edited by Prav_SQL Tuesday, July 30, 2013 3:34 AM updated comments
    • Marked as answer by Sam1980 Wednesday, July 31, 2013 1:42 AM
    Tuesday, July 30, 2013 3:34 AM
  • Hi

    We have a typo before year, idea is to match the key of the member

                          [Date].[Hierarchy].[Month].&[        2014               ]&[              12             ]

    StrToMember("[Date].[Hierarchy].[Month].&["+str(year(now()))+"]&["+str(month(now()))+"]")


    Prav

    • Marked as answer by Sam1980 Wednesday, July 31, 2013 1:42 AM
    Tuesday, July 30, 2013 4:25 AM
  • Hi

    I don't see any issues with MDX but having doubt on your Date dimension properties

    Check in SSAS solution

    1. Date dimension Type = Time

    2. Select Year attribute = Years

    3. Select Month attribute = Months

    plea refer to this http://www.msbiguide.com/2012/02/ytd-by-default-a-year-level-was-expected-no-such-level-was-found-in-the-cube-error-in-mdx/ for settings.


    Prav

    • Marked as answer by Sam1980 Wednesday, July 31, 2013 1:41 AM
    Tuesday, July 30, 2013 4:55 AM

All replies

  • Hi Sam,

    I think, this is similar to your previous requirement. provide your month key.

    1. expand month to members

    2. Drag and drop one key value on SSMS for understanding of underline key

    To my understanding, you should have quarter in your key


    Prav


    • Edited by Prav_SQL Tuesday, July 30, 2013 1:48 AM Updated comments
    • Marked as answer by Sam1980 Wednesday, July 31, 2013 1:42 AM
    Tuesday, July 30, 2013 1:47 AM
  • Thanks again Prav, I dragged a month value and got [Date].[Hierarchy].[Month].&[2014]&[12]

    So what do you think am missing?

    Tuesday, July 30, 2013 3:10 AM
  • Hi

    You did StrToMember("[Date].[Hierarchy].[Month].["+str(year(now()))+"].["+str(month(now()))+"]")

    but your key is [Date].[Hierarchy].[Month].&[2014]&[12]  (used & but not . )

    so we need to change the member construction as

    StrToMember("[Date].[Hierarchy].[Month].["+str(year(now()))+"]&["+str(month(now()))+"]")

    Happy to help!


    Prav


    • Edited by Prav_SQL Tuesday, July 30, 2013 3:34 AM updated comments
    • Marked as answer by Sam1980 Wednesday, July 31, 2013 1:42 AM
    Tuesday, July 30, 2013 3:34 AM
  • I tried the below in the WHERE clause and got the following error: Query (15, 66) Parser: The syntax for '&' is incorrect.

    Any thoughts?

    StrToMember("[Date].[Hierarchy].[Month].["+str(year(now()))+"]&["+str(month(now()))+"]")

    Tuesday, July 30, 2013 4:20 AM
  • Hi

    We have a typo before year, idea is to match the key of the member

                          [Date].[Hierarchy].[Month].&[        2014               ]&[              12             ]

    StrToMember("[Date].[Hierarchy].[Month].&["+str(year(now()))+"]&["+str(month(now()))+"]")


    Prav

    • Marked as answer by Sam1980 Wednesday, July 31, 2013 1:42 AM
    Tuesday, July 30, 2013 4:25 AM
  • Thanks again for your continued support. The query ran fine this time, but in the measure column, it is all "#Error". When I hover the mouse over it, it says "By default, a month level was expected. No such level was found in the cube"
    Tuesday, July 30, 2013 4:34 AM
  • Hi

    I don't see any issues with MDX but having doubt on your Date dimension properties

    Check in SSAS solution

    1. Date dimension Type = Time

    2. Select Year attribute = Years

    3. Select Month attribute = Months

    plea refer to this http://www.msbiguide.com/2012/02/ytd-by-default-a-year-level-was-expected-no-such-level-was-found-in-the-cube-error-in-mdx/ for settings.


    Prav

    • Marked as answer by Sam1980 Wednesday, July 31, 2013 1:41 AM
    Tuesday, July 30, 2013 4:55 AM