DATE in DATATABLE not working

[Greg_Deckler]

The documentation here:

https://msdn.microsoft.com/en-us/library/mt674921.aspx

Says that DATE(2009,1,1) is a valid value, but this returns an error.

VAR Holidays = DATATABLE("Date",DATETIME,
    {
        {DATE(2017,12,25)}
    })

The error is: 

"The tuple at index '1' from the table definition of the DATATABLE function does not have a constant expression in the column at index '1'. 

Either the documentation is in error or there is a bug or I can't read directions.

A single argument using Excel syntax for a one dimensional array constant, nested to provide an array of arrays. This argument represents the set of data values that will be in the table

For example,
{ {values in row1}, {values in row2}, {values in row3}, etc. }
Where {values in row1} is a comma delimited set of constant expressions, namely a combination of constants, combined with a handful of basic functions including DATE, TIME, and BLANK, as well as a plus operator between DATE and TIME and a unary minus operator so that negative values can be expressed.

The following are all valid values: 3, -5, BLANK(), DATE(2009,4,15)+TIME(2,45,21) Values may not refer to anything outside the immediate expression, and cannot refer to columns, tables, relationships, or anything else.

[v-qiuyu-msft]

Hi @Greg_Deckler,

I can reproduce the issue on my side as well. Already reported this issue internally: CRI 61666916. Will update here once I get any information. 

Best Regards,
Qiuyun Yu 

[v-qiuyu-msft]

Hi @Greg_Deckler,

Please see below information: 

This is a documentation bug. DATATABLE function only supports constant literals but not any other functions, even those which return a constant value. Please use the DAX table constructor syntax (https://msdn.microsoft.com/en-us/library/mt842623.aspx) instead:

{DATE(2009,4,15)+TIME(2,45,21)}

and

{DATE(2017,12,25)}

Since the table constructor syntax doesn't give user the ability to pick column names, the user can change column names afterwards or use DAX SELECTCOLUMNS function to rename the default column names to desired ones.

We'll fix the online document for DATATABLE.

Best Regards,
Qiuyun Yu 

[Greg_Deckler]

Ah, that's too bad, I'll have to figure out another way to incorporate holidays into my Net Working Days quick measure. Thanks for checking into this.

[Greg_Deckler]

OK, so the following works: I will see about updating my Quick Measure in the Gallery. Thanks!

NetWorkDaysHolidays = 
VAR Calendar1 = CALENDAR(MAX(NetWorkDays[created date]),MAX(NetWorkDays[review date]))
//VAR Holidays = DATATABLE("Date",DATETIME,{{}})
VAR Holidays = DATATABLE("Date",DATETIME,
    {
        {"12/25/2017 12:00:00 AM"}
    })
VAR Calendar2 = EXCEPT(Calendar1,Holidays)
VAR Calendar3 = ADDCOLUMNS(Calendar2,"WeekDay",WEEKDAY([Date],2))
RETURN COUNTX(FILTER(Calendar3,[WeekDay]<6),[Date])

[Reroo]

Hi,

@v-qiuyu-msft , you can use DATATABLE function to create a date column as well. You just need to enter the date in a specific format.

I could actually define a column with DATETIME datatype, where I entered the date as "YYYY-MM-DD" and it worked perfectly. You can later change it from datetime to just date format. So, here is a part of my code:

DATATABLE(
             "Accounting unit",INTEGER,
              "Location",STRING,
              "Opening date",DATETIME,
              "Closing date",DATETIME,
   {
         {1111,"Location 1","2010-11-1",},
         {2222,"Location 2","2010-11-1",}
   }
)

Cheers