Empty Date Value

[StoryofData]

Wondering if there a smart way to do this, I have table with cases and their open and closed dates. 

For the times when the case is still open, I want the card to show "Still Open", I have achieved this using two measures: 

Date Closed = CALCULATE(MAX('Query1'[DateClosed]))

Date Closed Blank = IF( ISBLANK([Date Closed]) , "Still Open", [Date Closed] )

Is there a smarter way to achieve this, without using two measures?

[lbendlin]

Your approach will only work when [Date Closed] is of type text. 

You can use COALESCE

Date Closed = FORMAT(CALCULATE(MAX('Query1'[DateClosed])),"MM/DD/YYYY")

Date Closed Blank = COALESCE([Date Closed] , "Still Open")

[StoryofData]

Thank you! So there is no way to have only one measure to achieve this?

[lbendlin · Accepted solution]

sure 🙂

Date Closed Blank = COALESCE(FORMAT(CALCULATE(MAX('Query1'[DateClosed])),"MM/DD/YYYY") , "Still Open")

[StoryofData]

For some reason, it produces a blank instead of "Still Open", meaning the card does not show anything at all. Could there be an issue?

=COALESCE(FORMAT(CALCULATE(MAX('Query1'[DateClosed])),"MM/DD/YYYY"),"Still Open")

[lbendlin]

Ah, right, I remember. There is a bug in FORMAT that happily returns an empty string instead of BLANK().

Oh well, back to your version.

=IF(ISBLANK(MAX('Query1'[DateClosed])),"Still Open",FORMAT(MAX('Query1'[DateClosed]),"MM/DD/YYYY"))

[StoryofData]

Thank you! Works now!

And, interesting, I guess that bug can be used to our advantage in some cases

[StoryofData]

Thank you!