cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
corbusier Regular Visitor
Regular Visitor

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

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.

 

pbi-date-table-error.PNG

5 REPLIES 5
Microsoft v-danhe-msft
Microsoft

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

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

1.PNG

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

1.PNG

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

 

Regards,

Daniel He

Community Support Team _ Daniel He
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
corbusier Regular Visitor
Regular Visitor

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

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? 

 

tempsnip.png

 

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

corbusier Regular Visitor
Regular Visitor

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

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.

Microsoft v-danhe-msft
Microsoft

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

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

 

Community Support Team _ Daniel He
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
aocall1 Visitor
Visitor

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

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:

Comma problem DAX.PNG

 

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!

Helpful resources

Announcements
Exclusive LIVE Community Event #3 – Ask Arun Anything

Exclusive LIVE Community Event #3 – Ask Arun Anything

Join us in the third Triple A event!

Meet the 2020 Season 1 Power BI Super Users!

Meet the 2020 Season 1 Power BI Super Users!

It’s the start of a new Super User season! Learn all about the new Super Users and brand-new tiered recognition system.

January 2020 Community Highlights

January 2020 Community Highlights

Make sure you didn't miss any of the things that happened in the community in January!

Top Solution Authors
Top Kudoed Authors