Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
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"
Solved! Go to Solution.
Hi @Anonymous ,
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:
Col_day =
IF (
'Table'[RFC data] = BLANK ()
|| 'Table'[Complete data] = BLANK (),
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.
Best Regards,
Henry
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Hi @Anonymous ,
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:
Col_day =
IF (
'Table'[RFC data] = BLANK ()
|| 'Table'[Complete data] = BLANK (),
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.
Best Regards,
Henry
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Hi,
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.
Hi @Anonymous
try it
Date_diff = IF('Table1'[RFC Date] = BLANK(), BLANK(), DATEDIFF([RFC Date], [Completed Date], DAY))
User | Count |
---|---|
125 | |
106 | |
99 | |
63 | |
62 |
User | Count |
---|---|
135 | |
116 | |
101 | |
71 | |
61 |