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
dirkjs
Frequent Visitor

Incorrect syntax for DATETIME

I get an incorrect syntax error for the DATETIME strings in the example below:

CustomFiscalPeriods =
DATATABLE (
"Fiscal YearNumber", INTEGER,
"FirstDayOfYear", DATETIME,
"LastDayOfYear", DATETIME,
{
{ 2016, "28-06-2015", "2016-07-02" },
{ 2017, "2016-07-03", "2017-07-01" },
{ 2018, "2017-07-02", "2018-06-30" },
{ 2019, "2018-07-01", "2019-06-29" }
}
)
 

The syntax for "2015-08-16" is invalid.

I tested with Power-BI versions of June and May as well what gives the same error message!
My Input method is:
English (United States) and Belgian (Period) keyboard.

11 REPLIES 11
v-lili6-msft
Community Support
Community Support

hi, @dirkjs 

If it is the format error as below:

CustomFiscalPeriods =
DATATABLE (
"Fiscal YearNumber", INTEGER,
"FirstDayOfYear", DATETIME,
"LastDayOfYear", DATETIME,
{
{ 2016, "28-06-2015", "2016-07-02" },
{ 2017, "2016-07-03", "2017-07-01" },
{ 2018, "2017-07-02", "2018-06-30" },
{ 2019, "2018-07-01", "2019-06-29" }
}
)

Please adjust it as below, then it should work well.

Table = 
DATATABLE (
    "Fiscal YearNumber", INTEGER,
    "FirstDayOfYear", DATETIME,
    "LastDayOfYear", DATETIME,
    {
        { 2016, "2015-06-28", "2016-07-02" },
        { 2017, "2016-07-03", "2017-07-01" },
        { 2018, "2017-07-02", "2018-06-30" },
        { 2019, "2018-07-01", "2019-06-29" }
    }
)

Result:

10.JPG

 

Best Regards,

Lin

Community Support Team _ Lin
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
pbeye
Resolver II
Resolver II

I don't see "2015-08-16" in your formula, but I see "28-06-2015". Maybe that's the one causing the issue? Actually, when I test it out on my desktop both of those work for me. I'm using the July 2019 version.

 

Maybe try adjusting it to match the other dates formats to see if that works.

dirkjs
Frequent Visitor

Thanks for your response, but, both notations "28-06-2015" as "2015-06-28" do not work!

Can you post a screen shot of what you see? It workes fine for me which is weird. You're trying to create a new table and not a column, correct?

dirkjs
Frequent Visitor

That is true. I try to create a table with 3 columns.

Incorrect Syntax for DATETIMEIncorrect Syntax for DATETIME

dirkjs
Frequent Visitor

This is something for Power BI Development Team. I tried to comment the second field and I got the following strange result!Second field as a comment.Second field as a comment.

hi, @dirkjs 

Could you try my sample pbix to check it if works well.

2.JPG

 

Best Regards,

Lin

 

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

Dear Lin,

Thanks for your kind support. When I load your demo.pbx, the database is created and loaded, BUT, the syntax error remains(!) and when I hit one [RETURN] in the formula panel, I loose the data in the tabel like you see in my screenshot below.Annotation 2019-08-19 104829.png

dirkjs
Frequent Visitor

I have more syntax errors! This file is from the website Power BI Tips & Tricks, 

File – DAX Date Tables PBIX

See the following screen shot!

Error, BugError, Bug

dirkjs
Frequent Visitor

I could find it. It is really a BUG in Power BI or even DAX. If I add a space before the comma seperator like you see below, there are no syntax errors anymore! So Microsoft developpers, you know what to do.Annotation 2019-09-19 114742.png

dirkjs
Frequent Visitor

The same is true for the DATETIME function. Add a space before the comma like below and the problem is solved.Annotation 2019-09-19 115402.png

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.