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

[Ashish_kumar12]

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 

[v-lili6-msft · Accepted solution]

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:

BeforeAfter

Best Regards,

Lin

[amitchandak]

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]

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 

[v-lili6-msft · Accepted solution]

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:

BeforeAfter

Best Regards,

Lin

[Ashish_kumar12 · Accepted solution]

Hi Lin,

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

Regards,

Ashish

[amitchandak]

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