Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
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 :
Solved! Go to 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:
Best Regards,
Lin
Hi Lin,
Thank you for your help. It works and i managed to make this column.
Regards,
Ashish
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
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:
Best Regards,
Lin
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
User | Count |
---|---|
125 | |
106 | |
99 | |
63 | |
62 |
User | Count |
---|---|
135 | |
116 | |
101 | |
71 | |
61 |