Cannot convert value '' of type text to type Date

[SK_JOB]

 

Hello,

I am getting this error : Cannot convert value '' of type text to type Date. for this formula:

_Rev. Rec. Term in Days =
DATEDIFF ( [_Rev. Rec. Start Date], [_Rev. Rec. End Date], DAY )

Where:

_Rev. Rec. Start Date =
FORMAT (
    DATEVALUE ( MIN ( 'Transaction Lines'[Rev. Rec. Start Date] ) ),
    "mm/dd/yyyy"
)

and

_Rev. Rec. End Date =
FORMAT (
    DATEVALUE ( MIN ( 'Transaction Lines'[Rev. Rec. End Date] ) ),
    "mm/dd/yyyy"
)

Rev. Rec. Start Date and Rev. Rec. End Date are of Date Datatype.


I tried this:

_Rev. Rec. Term in Days =
IF (
    ISBLANK ( DATEDIFF ( [_Rev. Rec. Start Date], [_Rev. Rec. End Date], DAY ) ),
    BLANK (),
    DATEDIFF ( [_Rev. Rec. Start Date], [_Rev. Rec. End Date], DAY )
)and this:

_Rev. Rec. Term in Days =
IF (
    ISBLANK ( DATEDIFF ( [_Rev. Rec. Start Date], [_Rev. Rec. End Date], DAY ) ),
    0,
    DATEDIFF ( [_Rev. Rec. Start Date], [_Rev. Rec. End Date], DAY )
)

But still get the same error.

TIA,

Learner

[HotChilli]

Is this a calculated column or a measure?

DATEDIFF requires dates and when you add FORMAT you force it to a Text value, so start there.

[SK_JOB]

All the _(underscore ones) are measures:

_Rev. Rec. Start Date

_Rev. Rec. End Date

_Rev. Rec. Term in Days

_Rev. Rec. Term in Months = DATEDIFF([_Rev. Rec. Start Date],[_Rev. Rec. End Date],MONTH)+1

So I created these measures because there was nulls in these date values coming from database and I had to subtract end date with the start date.

I think you have a point with FORMAT. So how would you propose to subtract days where we have nulls in the database without creating a measure?

Thanks for your help.