cancel
Showing results for
Did you mean:
Frequent Visitor

## Similar equation producing error message in one column

I am calculating the amount of time to close a case during business hours which is a dynamic number based on the group.  I am using a similar function to do so while only changing the open date and closed date for the two equations but receive an error message with the second equation.

*FirstDayEndTime =
DATE ( YEAR ('Case'[CreatedDate]), MONTH ('Case'[CreatedDate]), DAY ('Case'[CreatedDate]))
& " " & TIME(HOUR(Case[BusHrEnd]), MINUTE(Case[BusHrEnd]), SECOND(Case[BusHrEnd]))

This equation works perfectly in combining the date and hour closed together.

*LastDayEndTime =
DATE ( YEAR ('Case'[ClosedDate]), MONTH ('Case'[ClosedDate]), DAY ('Case'[ClosedDate]))
& " " & TIME(HOUR(Case[BusHrEnd]), MINUTE(Case[BusHrEnd]), SECOND(Case[BusHrEnd]))

This equation produces the follow error message:

Cannot convert value '' of type Text to type Date.

Does anyone have any suggestions?  All columns are formated the same.
1 ACCEPTED SOLUTION

Accepted Solutions
Established Member

## Re: Similar equation producing error message in one column

Your second formula breaks probably because of null or blank values in the ClosedDate field. You may want to add a check using IF condition in your formula if that is the case.

e.g.

LastDayEndTime = IF(ISBLANK('Table'[Date]), BLANK(), 'Table'[Date] + 'Table'[Time])

Regards,

Tarun

2 REPLIES 2
Established Member

## Re: Similar equation producing error message in one column

Your second formula breaks probably because of null or blank values in the ClosedDate field. You may want to add a check using IF condition in your formula if that is the case.

e.g.

LastDayEndTime = IF(ISBLANK('Table'[Date]), BLANK(), 'Table'[Date] + 'Table'[Time])

Regards,

Tarun

Frequent Visitor

## Re: Similar equation producing error message in one column

That did the trick, thank you Tarun

Announcements

#### Coming Soon: T-Shirt Design Contest

Keep your eyes open for our upcoming T-shirt design contest!

#### Meet the 2020 Season 1 Power BI Super Users!

It’s the start of a new Super User season! Learn all about the new Super Users and brand-new tiered recognition system.

#### Super User Challenge: Can You Solve These?

We're celebrating the start of the New Super User season with our first ever Super User 'Can You Solve These?' challenge.

#### Power BI Desktop Update - February 2020

We are super excited for our update this month, as we are releasing two of our top community requests!