I have 2 date columns: RFC Date and Completed date, with some missing dates.
Currently using this DAX to create a new column
Date_diff = IF('Table1'[RFC Date] = BLANK(), "", (DATEDIFF([RFC Date], [Completed Date], DAY)))
The above gives me an error "expression that yield variant data-type cannot be used to define calculated columns"
Go to Solution.
Hi @maayya007 ,
The reason for the above result is that "" is not supported. For this type of format, if the date field is empty, use blank() instead of "". I did the following test:
'Table'[RFC data] = BLANK ()
|| 'Table'[Complete data] = BLANK (),
DATEDIFF ( 'Table'[RFC data], 'Table'[Complete data], DAY )
In addition, before writing the formula, make sure that the referenced field is a date type, otherwise an error will be reported.
If the problem is still not resolved, please provide detailed error information and let me know immediately. Looking forward to your reply.
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
Check the data type of the entries in the RFC Date and Completed Date columns. They are probably not proper dates. In the Query Editor, ensure that those 2 columns are set as Date columns.
Date_diff = IF('Table1'[RFC Date] = BLANK(), BLANK(), DATEDIFF([RFC Date], [Completed Date], DAY))
Check out new user group experience and if you are a leader please create your group!
Check out how to claim yours today!
Test your skills now with the Cloud Skills Challenge.