Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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))
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
109 | |
98 | |
77 | |
66 | |
54 |
User | Count |
---|---|
144 | |
104 | |
100 | |
86 | |
64 |