My commas are being seen as periods in a DAX formula!

[corbusier]

I'm getting a weird error in Power BI Desktop. I'm trying to add a Date table from this site: https://www.sqlbi.com

However, I am not able to generate the Calendar table because my commas are being seen as periods. I have deleted the relevant parts and typed it in myself, and some commas are being seen as periods, and others as commas. However, of course it doesn't work. In this screenshot I've added extra arguments to show how it looks in the error message.

[v-danhe-msft]

Hi @corbusier,

Based on my test, it could work on my side:

CALENDAR 4 = 
VAR BASECALENDAR =
    CALENDAR ( DATE ( 2016, 1, 1 ), DATE ( 2018, 12, 31 ) )
VAR RENAMEDCALENDAR =
    SELECTCOLUMNS ( BASECALENDAR, "CALENDAR[DATE]", [DATE] )
VAR CALENDAR_1 =
    SELECTCOLUMNS (
        RENAMEDCALENDAR,
        "DATE", 'CALENDAR'[DATE],
        "YEAR", YEAR ( CALENDAR[DATE] ),
        "MONTH NUMBER", MONTH ( CALENDAR[DATE] ),
        "MONTH", FORMAT ( CALENDAR[DATE], "MMMM" ),
        "YEAR MONTH", FORMAT ( CALENDAR[DATE], "MMM YY" )
    )
VAR CALENDAR_2 =
    ADDCOLUMNS ( CALENDAR_1, "YEAR MONTH NUMBER", [YEAR] * 12 + [MONTH NUMBER] - 1 )
RETURN
    CALENDAR_2

It seems that the data format in your formula is not correct, you could check the picture below:

You could also download the pbix filet to have a view.

Regards,

Daniel He

[corbusier]

Hi Daniel, I believe it could be something to do with regional settings since I'm in Germany but my Power BI installation is set to English. Could it be? 

As you can see in the Snip, my commas aren't being detected correctly. Some commas are detected as periods, some as commas.

[Anonymous]

Hi all,

I know this is an older post but I thought I'd comment to say this is happening to me as well.

I'm based in the Netherlands and my PC regional settings for "Regional Format" are set to English (Netherlands). Find it in Settings -> Time & Language -> Region

This is causing single commas to sometimes be converted to period/full stops as below:

To work around this I can write two commas instead: 

Dates = CALENDAR (

              DATE ( YEAR ( MIN ( Sales[Order Date] ) ), 1,, 1 ),

              DATE ( YEAR ( MAX (Sales[Order Date] ) ), 1,, 1 )

)

It is possible to change your Regional Format to another location, but beware that if anyone in your region tries to edit your pbix file later they will have problems.

Hope this helps someone in the future!

[corbusier]

I saw that the input could be in the form of a string so I side-stepped the problem here. 

CALENDAR("2014-01-01","2018-12-31")

I'm hesitant to mark this as solved since the fundamental issue is still a mystery to me, but it works for this particular instance of the problem.

[v-danhe-msft]

Hi @corbusier,

I could not reproduce your problem and withe the formula you mentioned also could work.

CALENDAR("2014-01-01","2018-12-31")

if your problem could be solved, could you please mark the helpful replies as Answered?

Regards,

Daniel He