cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Ashish_kumar12 Frequent Visitor
Frequent Visitor

Error:An argument of function 'DATE' has the wrong data type or the result is too large or too small

Hi All,

I am trying make an new column where i want to compare today's week num and year with Date of birth of candidates (i.e 18-sep-1989 with today's date(18-Sep-2019). However i am getting an error which is:

 

"An argument of function 'DATE' has the wrong data type or the result is too large or too small "

Formula which i am using is :

DOB_View = IF(WEEKNUM(DATE(YEAR(TODAY()),MONTH(Union_Table[field_date_of_birth]),DAY(Union_Table[field_date_of_birth])),2)=WEEKNUM(TODAY(),2),"This Week","All Others")
 
Can anyone help me with correct solution ?
Thanks
Ashish 
2 ACCEPTED SOLUTIONS

Accepted Solutions
Community Support Team
Community Support Team

Re: Error:An argument of function 'DATE' has the wrong data type or the result is too large or too s

hi, @Ashish_kumar12 

This error is because there are blank value in field_date_of_birth column, just adjust your formula as below:

DOB_View = IF(ISBLANK(Union_Table[field_date_of_birth])=FALSE(),
IF (
    WEEKNUM (
        DATE ( YEAR ( TODAY () ), MONTH ( Union_Table[field_date_of_birth] ), DAY ( Union_Table[field_date_of_birth] ) ),
        2
    )
        = WEEKNUM ( TODAY (), 2 ),
    "This Week",
    "All Others"
)
, "All Others")

Result:

5.JPGBefore6.JPGAfter

 

Best Regards,

Lin

Community Support Team _ Lin
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

View solution in original post

Ashish_kumar12 Frequent Visitor
Frequent Visitor

Re: Error:An argument of function 'DATE' has the wrong data type or the result is too large or too s

Hi Lin,

 

Thank you for your help. It works and i managed to make this column.

 

Regards,

Ashish

View solution in original post

5 REPLIES 5
amitchandak Super Contributor
Super Contributor

Re: Error:An argument of function 'DATE' has the wrong data type or the result is too large or too s

I tried the same on a date field and it worked. Just check DOB is of data type or not

 

Week No Compare = if(WEEKNUM(date(YEAR(TODAY()),MONTH(Sales[Sales Date]),day(Sales[Sales Date])),2)=WEEKNUM(TODAY(),2) ,"This Week","All Other")

Or the position of parenthesis

 

Ashish_kumar12 Frequent Visitor
Frequent Visitor

Re: Error:An argument of function 'DATE' has the wrong data type or the result is too large or too s

Hi Amit,

 

Thank you for your revert.

 

Do you changed the data type from text to Date?

 

As it still giving me the same errors. Please see below screenshot for the same 

Capture.PNG

 

amitchandak Super Contributor
Super Contributor

Re: Error:An argument of function 'DATE' has the wrong data type or the result is too large or too s

I think it is still taking it as a text. Use format and convert to date in a new column

 

check example on this

https://community.powerbi.com/t5/Desktop/Converting-from-Text-to-Date/td-p/133251

Community Support Team
Community Support Team

Re: Error:An argument of function 'DATE' has the wrong data type or the result is too large or too s

hi, @Ashish_kumar12 

This error is because there are blank value in field_date_of_birth column, just adjust your formula as below:

DOB_View = IF(ISBLANK(Union_Table[field_date_of_birth])=FALSE(),
IF (
    WEEKNUM (
        DATE ( YEAR ( TODAY () ), MONTH ( Union_Table[field_date_of_birth] ), DAY ( Union_Table[field_date_of_birth] ) ),
        2
    )
        = WEEKNUM ( TODAY (), 2 ),
    "This Week",
    "All Others"
)
, "All Others")

Result:

5.JPGBefore6.JPGAfter

 

Best Regards,

Lin

Community Support Team _ Lin
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

View solution in original post

Ashish_kumar12 Frequent Visitor
Frequent Visitor

Re: Error:An argument of function 'DATE' has the wrong data type or the result is too large or too s

Hi Lin,

 

Thank you for your help. It works and i managed to make this column.

 

Regards,

Ashish

View solution in original post

Helpful resources

Announcements
Ask Amanda Anything Q&A

Ask Amanda Anything Q&A

Learn the answers to some of the questions asked during the Amanda Triple A event.

October 2019 Community Highlights

October 2019 Community Highlights

October was a busy month in the community. Read the recap article to learn about some of the events and content.

New Solution Badges

New Solution Badges

Two waves of brand new solution badges are coming! Read the article for more information on our new community badges.

Analytics in Azure virtual event

Analytics in Azure virtual event

Experience a limitless analytics service built to ingest, prep, manage, and serve data for immediate use in Power BI.

Users Online
Currently online: 342 members 3,474 guests
Please welcome our newest community members: