Building a Date from Year, Month, and Day columns

[ahalvers]

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:

https://docs.microsoft.com/en-us/dax/date-function-dax

but I'm not having any luck resolving the error.

[Anonymous · Accepted solution]

@ahalvers 

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

Then stick with the DAX.

Best,
Paul

[az38]

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

[ahalvers]

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]

@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

[ahalvers]

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.

[amitchandak]

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

[ahalvers]

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]

@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