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
Boingphiwp
Regular Visitor

DAX Date function Direct Query

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.

 

 

 

 

1 ACCEPTED SOLUTION
Phil_Seamark
Employee
Employee

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


To learn more about DAX visit : aka.ms/practicalDAX

Proud to be a Datanaut!

View solution in original post

2 REPLIES 2
Phil_Seamark
Employee
Employee

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


To learn more about DAX visit : aka.ms/practicalDAX

Proud to be a Datanaut!

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)

 

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.