none
Getting #Error when attempting YTD calculation

    General discussion

  • Below is a screen print showing my folders, my MDX query and my result set.

    I'm trying to list months on the ROWS and show 1) monthly sales and 2) YTD sales on the COLUMNS. But I get an error on the YTD calculation and I can't figure out why. Any help would be appreciated. 

    Here is the code shown in the below screen print:

    WITH
    MEMBER [Measures].[YTD_SALES] AS
        sum(
        YTD(
        [Date - Invoice].[Calendar Date Rollup].[Year Month].CurrentMember),
        CoalesceEmpty([Measures].[Extended Price], 0))
        
        select {[Measures].[Extended Price] ,
        
        [Measures].[YTD_SALES]} on columns,

    [Date - Invoice].[Year Month].members ON ROWS

    from [Sales History]
    where [Date - Invoice].[Year].&[2013]


    • Edited by JohnZofo Tuesday, July 02, 2013 9:11 PM
    Tuesday, July 02, 2013 9:09 PM

All replies

  • Hi

    Try this and provide the erroe message of the cell for better understanding

    WITH
     MEMBER [Measures].[YTD_SALES] AS
         sum(
         YTD(
         [Date - Invoice].[Calendar Date RollUp].CurrentMember),
           [Measures].[Sales Amount] ) 
        
         select {[Measures].[Sales Amount] ,
         
         [Measures].[YTD_SALES]} on columns,
     NON EMPTY{
    			[Date - Invoice].[Calendar Date RollUp].[Year Month].members 
    		}ON ROWS
     
    from (
      SELECT  [Date - Invoice].[Calendar Date RollUp].[Year].&[2013] ON 0
      FROM [Sales History]
      )


    Prav

    Tuesday, July 02, 2013 11:02 PM
  • Prav, thanks for your reply. Your request lead me to figure out the problem. The error was:

    'The CURRENTMEMBER function expects a hierarchy expression for the 1 argument. A member expression was used'.

    I realized that my use of [Year MONTH] in line 5 was unnecessary and was causing this error. When coding in MDX there is a temptation to always identify the level that you are working on because it seems like necessary information. It's not! It only throws an error. The code already 'knows' the level that I am working on because of my statement in the 'ROWS' section. As the error says, it only wants a hierarchy expression with no need for me to list the level [ Year Month] in the hierarchy. This kind of error has bitten me several times now but this time I really get it. So the correct code is:

    WITH
    MEMBER [Measures].[YTD_SALES] AS
        sum(
        YTD(
        [Date - Invoice].[Calendar Date Rollup].CurrentMember),
        CoalesceEmpty([Measures].[Extended Price], 0)),  FORMAT_STRING = '$#,###.00'
        
        select {[Measures].[Extended Price] ,
        
        [Measures].[YTD_SALES]} on columns,

    [Date - Invoice].[Year Month].members ON ROWS

    from [Sales History]
    where [Date - Invoice].[Year].&[2013] 

    (I've added a formatting statement to this code as well.)

    Thanks,

    John

    Wednesday, July 03, 2013 3:27 PM