# Formula Invalid

• ### 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],

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

Alex

Monday, May 11, 2015 6:23 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/

Monday, May 18, 2015 3:08 AM

### 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
• 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.

__________________________________________________________

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
• 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/

Monday, May 18, 2015 3:08 AM