cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
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
v-danhe-msft Super Contributor
Super Contributor

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.

v-danhe-msft Super Contributor
Super Contributor

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.
Highlighted
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
Ask Amir Anything

Exclusive LIVE Community Event No. 2 – Ask Amir Anything

Next in our Triple A series: Ask Amir Netz questions about the latest updates, features and future.

Ask Amanda Anything Q&A

Ask Amanda Anything Q&A

Learn the answers to some of the questions asked during the Amanda Triple A event.

October 2019 Community Highlights

October 2019 Community Highlights

October was a busy month in the community. Read the recap article to learn about some of the events and content.

New Solution Badges

New Solution Badges

Two waves of brand new solution badges are coming! Read the article for more information on our new community badges.

Users Online
Currently online: 166 members 1,727 guests
Please welcome our newest community members: