Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
corbusier
Advocate IV
Advocate IV

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

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.

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.

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!

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.

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.

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.