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

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

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:

BeforeBeforeAfterAfter

 

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

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 User
Super User

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

 

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

 

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:

BeforeBeforeAfterAfter

 

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.

Hi Lin,

 

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

 

Regards,

Ashish

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

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.