Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Get certified in Microsoft Fabric—for free! For a limited time, the Microsoft Fabric Community team will be offering free DP-600 exam vouchers. Prepare now

Reply
Anonymous
Not applicable

Cannot convert value '' of type text to type Date

 

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

2 REPLIES 2
HotChilli
Super User
Super User

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.

Anonymous
Not applicable

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.

Helpful resources

Announcements
OCT PBI Update Carousel

Power BI Monthly Update - October 2024

Check out the October 2024 Power BI update to learn about new features.

September Hackathon Carousel

Microsoft Fabric & AI Learning Hackathon

Learn from experts, get hands-on experience, and win awesome prizes.

October NL Carousel

Fabric Community Update - October 2024

Find out what's new and trending in the Fabric Community.