Find datediff with missing dates

[Anonymous]

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"

[v-henryk-mstf · Accepted 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.

[Ashish_Mathur]

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.

[Vera_33]

Hi @Anonymous 

try it

Date_diff = IF('Table1'[RFC Date] = BLANK(), BLANK(), DATEDIFF([RFC Date], [Completed Date], DAY))