Reply
Topic Options
- Subscribe to RSS Feed
- Mark Topic as New
- Mark Topic as Read
- Float this Topic for Current User
- Bookmark
- Subscribe
- Printer Friendly Page

Anonymous
Not applicable
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

Cannot convert value '' of type text to type Date
06-07-2023
10:02 AM
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
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
06-07-2023
10:25 AM

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.
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
06-07-2023
10:36 AM

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?
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
Power BI Monthly Update - July 2025
Check out the July 2025 Power BI update to learn about new features.

Featured Topics
Top Solution Authors (Last Month)
User | Count |
---|---|
72 | |
66 | |
34 | |
25 | |
22 |
Top Kudoed Authors (Last Month)
User | Count |
---|---|
97 | |
96 | |
58 | |
45 | |
42 |