DATEADD Syntax error DAX argument separator comma semicolon

[NicoNAB]

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

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

[Anonymous]

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!

[AMS77]

I can confirm the same issue. My language settings were set to English - Dutch  & priorly working time intelligence work broken as described above. Switching to English - US via windows settings fixed the issue

[Chris12]

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

[v-sihou-msft]

@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:

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