DAX Date function Direct Query

[Boingphiwp]

Issue encountered:

DAX function "Date" I believe has a 16 bit integer limitation on the days compenent.

Explanation:

I.e. Date(year,month,days)

I am inputting 1900,1, [epoch days colum]

My data set counts the number of days since 1900/1/1 (ranges from about 38000 - 44000.

The maximum Date i have been able to return is: 17 September 1989.

This equates to 32,766 days.

I find it incredibly likely that since the normal range of a 16 bit integer is −32,768 to 32,767

That the maximum Value for the days" component of the "Date" function is a 16 bit value.

The problem I am engaging with:

I have a list of numbers in an SQL server that correspond to the number of days since 1/1/1.

I have created a new column (by simply minusing 693596) that gives me the number of days since 1/1/1900.

Data typically ranges from 1 - 44,000

I am also operating in direct query mode which is limiting my capabilities. 

I am trying to find a solution in either query editor or report editor.

[Phil_Seamark · Accepted solution]

HI @Boingphiwp

The 3rd parameter of the DATE function just represents the calendar day.

eg. if today is the 6th of April, 2018.  This would be represented as DATE(2018,4,6) and is not really designed for using the way you are.

However why not try this instead.

DATE(1900,1,1) + 40000

[Boingphiwp]

Thanks for your reply Phil,

Your solution worked. It also led me to discover an erroneous peice of data;

I tried this, and when working with an individual larger number as you've suggested it worked.

However, when I try and calculate it based on another column

e.g. FmtEntryDate = DATE(1900,1,1)+[1900days]

I receive the following error: 

Arithmetric overflow error converting expression to data type datetime.. The exception was raised by the IDataReader interface

However; I have since discovered 1 erroneous data entry of "-693000", which when excluded using an if statment in the new colum,

Allowed my function to work with the direction you provided.

Thank you kindly.

Edit: (the original data is calculating epoch days since 1/1/1)