cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
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
Super User IV
Super User IV

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

 

Super User IV
Super User IV

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
Exclusive LIVE Community Event #3 – Ask Arun Anything

Exclusive LIVE Community Event #3 – Ask Arun Anything

Join us in the third Triple A event!

Meet the 2020 Season 1 Power BI Super Users!

Meet the 2020 Season 1 Power BI Super Users!

It’s the start of a new Super User season! Learn all about the new Super Users and brand-new tiered recognition system.

Super User Challenge: Can You Solve These?

Super User Challenge: Can You Solve These?

We're celebrating the start of the New Super User season with our first ever Super User 'Can You Solve These?' challenge.

January 2020 Community Highlights

January 2020 Community Highlights

Make sure you didn't miss any of the things that happened in the community in January!

Top Solution Authors