none
Formula Invalid RRS feed

  • Question

  • Hi

    I created a measure [12mSalesCumulation] to calculate the cumulation of the 12m sales.

    IF(NOT(ISBLANK('Customer Information'[12mSales])),
    SUMX (
    FILTER (
    ALL('Customer Information'[End Customer ID]),
    'Customer Information'[12mSales]
    >= CALCULATE (
    'Customer Information'[12mSales],
    VALUES ( 'Customer Information'[End Customer ID])
    )
    ),
    'Customer Information'[12mSales]
    ))

    The measure works fine in another similar database I created before, and when I checked the formula, it shows no error as follow.

    However, when I added it into the value pane in Pivot, it showed "the formula is invalid".

    P.S. the measure [12mSales]=

    CALCULATE(SUM(Sales[Sales Amount]),
    DATESBETWEEN(
    Calendar[Date],
    DATEADD(NEXTDAY(ENDOFMONTH(Calendar[Date])),-12,MONTH),
    DATEADD(LASTDATE(STARTOFMONTH(Calendar[Date])),+1,MONTH)))

    I'm not sure where went wrong. Can someone help with this?

    Thanks in advance

    Alex

    Monday, May 11, 2015 6:23 AM

Answers

All replies

  • did you miss the equal sign ("=") at the beginning?

    maybe its a language issue - "," vs. ";" as separator?

    -gerhard


    Gerhard Brueckl
    blogging @ http://blog.gbrueckl.at
    working @ http://www.pmOne.com

    Wednesday, May 13, 2015 12:17 PM
    Answerer
  • Hey Alex,

    you need to change the seperator within your formula from , to ;

    Check out this one:

    IF(NOT(ISBLANK('Customer Information'[12mSales]));
    SUMX (
    FILTER (
    ALL('Customer Information'[End Customer ID]);
    'Customer Information'[12mSales]
    >= CALCULATE (
    'Customer Information'[12mSales];
    VALUES ( 'Customer Information'[End Customer ID])
    )
    );
    'Customer Information'[12mSales]
    ))

    I hope I could help.

    __________________________________________________________

    Please mark helpful posts or answers


    Wednesday, May 13, 2015 1:03 PM
  • Hi Gerhard,

    The equal sign is in the formula, and there is error message "The syntax for ';' is incorrect." when I replaced the separator.

    Alex

    Friday, May 15, 2015 2:31 AM
  • Hi Matthaeus,

    It seems the separator ";" is incorrect in the formula.

    Alex

    Friday, May 15, 2015 2:33 AM
  • the syntax itself is OK, you can always check it for example here: www.daxformatter.com

    depending on your local language settings, as I said, you need to use ";" or "," as your separator for all formula parameters

    the formula also does not contain any newer DAX function so I am quite sure the error relates to the separators

    -gerhard


    Gerhard Brueckl
    blogging @ http://blog.gbrueckl.at
    working @ http://www.pmOne.com

    Friday, May 15, 2015 7:38 AM
    Answerer
  • Hi Gerhard,

    I'm not sure how to fix it. Does it mean I need to check all measures to see if there is any inconsistent separator and change it?

    The local language is English. The same measures I used in another workbook work perfectly fine.

    Thanks,
    Alex

    Friday, May 15, 2015 9:57 AM
  • Hi Alex,

    I created a sample data source based on the information you provided, and created those two measure by just coping your formula, and it worked. Therefore, at least I can verify your formula is correct.

    Based on that, we can consider the another possible cause of this error message:

    The workbook got the problem might be out of sync with the source data.

    To fix it, try to refresh the data in the workbook, for example, right click a cell in the pivot and click Refresh.

    You may want to read more from the following link:
    http://www.powerpivotpro.com/2010/08/two-common-powerpivot-error-messages/

    and also this:
    http://www.powerpivot-info.com/post/411-changing-how-powerpivot-handles-dax-errors

    Monday, May 18, 2015 3:08 AM