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

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
SK_JOB
Regular Visitor

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.

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
Europe Fabric Conference

Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

Power BI Carousel June 2024

Power BI Monthly Update - June 2024

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

RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.