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
ahalvers
New Member

Building a Date from Year, Month, and Day columns

I have columns of Year, Month, and Date, all numbers.  I want to build a column that's recognized as a Date.  I'm using:

 

Date = DATE(BranchTotals[Year],BranchTotals[Month],BranchTotals[Day])
 
but I get the error "An argument of function 'DATE' has the wrong data type or the result is too large or too small."
 
I'm trying to follow some examples online, and also the instructions from here:
 
but I'm not having any luck resolving the error.
1 ACCEPTED SOLUTION
V-pazhen-msft
Community Support
Community Support

@ahalvers 

All you had to do is change the three columns to whole number data type.

Then stick with the DAX.

 

building date.JPG

 

Best,
Paul

View solution in original post

8 REPLIES 8
V-pazhen-msft
Community Support
Community Support

@ahalvers 

All you had to do is change the three columns to whole number data type.

Then stick with the DAX.

 

building date.JPG

 

Best,
Paul

az38
Community Champion
Community Champion

Hi @ahalvers 

be sure that all vaues in your Year, Month and Day arent empty, arent null and have a WHole Number Data Type in Power Query mode


do not hesitate to give a kudo to useful posts and mark solutions as solution
LinkedIn

In QueryMode I have a column "YearMonth" that looks like 201405 so, May 2014.  In Dax I use the LEFT(YearMonth,4) function to create Year, and Right(YearMonth,2) to create the Month column.  From the query I get a Day column although it does have some day entries of 0 that I filter out.  I don't have any null or empty, but I don't know how to force that the columns I'm parsing have whole number format. 

az38
Community Champion
Community Champion

@ahalvers 

where did you filter out 0 days? if in visual it will not work because the issue will be exists in store data 

are you sure you have a correct 31th days of month? for example dont you have a 31 Feb for example?

you can also remove all sensitive data from your pbix-file and share it to cloud fileservice to investigate


do not hesitate to give a kudo to useful posts and mark solutions as solution
LinkedIn

Update:

Year and Month were text columns, I've now formatted them as Whole Number.  I've also validated that Day only has #s 1-31, Month has 1-12, and Year has 2012-2020.  However, the error persists.

If possible please share a sample pbix file after removing sensitive information.Thanks.

I edited the Dax formula to:

Date = DATE(Year(BranchTotals[Year]),Month(BranchTotals[Month]),Day(BranchTotals[Day]))
and the error disappears, however the dates are wrong.  E.g.:
Year: 2014
Month: 5
Day: 23
Result: Sunday, January 22, 1905
 
az38
Community Champion
Community Champion

@ahalvers 

statement DATE(Year(BranchTotals[Year]),Month(BranchTotals[Month]),Day(BranchTotals[Day])) 

is completely incorrect because you are trying to get YEAR() from whole number value 2014. it will not work


do not hesitate to give a kudo to useful posts and mark solutions as solution
LinkedIn

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.