cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
NicoNAB New Member
New Member

DATEADD Syntax error DAX argument separator comma semicolon

Hi All,

 

I am having an annoying issue with DATEADD syntax, specifically with the "Interval" term.

The syntax of DATEADD is DATEADD(DimDate[DateKey], Number of Intervals, Interval)

Where for intervals the optional values are: day, year, month, etc.

The issue is that I cannot manage to insert the Interval optional value correctly (formula bar does not pop out the optional values automatically as usual, when i go into the Interval term. Hence I have to type MONTH manually. But the term is rejected and i get the following syntax error:

 

Measure= DATEADD('Date'[Date], -1, MONTH)

The syntax for 'MONTH' is incorrect. (DAX(DATEADD('Date'[Date],-1. MONTH))).

 

Please note how the syntax error message is replacing the comma for a dot after -1. So something is going on with the separator which is explaining that the option box for the interval values is not popping out automatically and then the syntax error...

 

Captura.JPG

 

I have used this formula previously in another PC and works just fine (no syntax errors).

 

Please note, that due to regional settings, My Excel formula default separator is a semicolon, not comma. But PowerBI desktop Syntax requests comma instead of Excels default semicolon. I think this has probably something to do with the above issue since the other PC I own where DATEADD is working fine, somehow uses semicolon on both Excel and PowerBI.

 

Is there a way to force Power BI into using ; instead of , as argument separator?

 

Please let me know if you can figure this out. Many thanks.

 

Nicolas

3 REPLIES 3
Moderator v-sihou-msft
Moderator

Re: DATEADD Syntax error DAX argument separator comma semicolon

@NicoNAB

 

In Power BI, it only accepts comma as separator. Currently you have to replace all semicolon into comma. You can submit a feature request on Idea

 

However, in this scenario, since you want to return the previous month date based on row level data, you should create a calculated column instead of measure. Otherwise it will still throw error like below:

Capture00.PNG

It should work by using calculated column, and it will prompt interval automatically when you input last comma.

 

Capture01.PNG

 

Chris12 Frequent Visitor
Frequent Visitor

Re: DATEADD Syntax error DAX argument separator comma semicolon

Hi Nico,

 

I just encountered the same problem and found out that simply putting a space " " behind the -1, in your case, solves the problem. So

 

Measure= DATEADD('Date'[Date], -1 , MONTH)

should do the trick.

 

(I also had some other non-US regional settings that might have caused this. Unfortunately, even setting everything back to en-US did not help. So I guess I'll have to live with this ugly workaround.)

 

Chris

NielsvWingerden Frequent Visitor
Frequent Visitor

Re: DATEADD Syntax error DAX argument separator comma semicolon

Hi Nicolas,

 

I had exactly the same issue over here, and yes you would think this is related to the settings in Power BI however it are the Windows language settings that are responsible for this behaviour.

To solve the issue go to "Language settings" and change the Windows language setting to "English (United States)", you probably will be asked to sign out. After signing in again you will notice that you can now use the comma as seperator.

Succes!