Show Blank Date Value with DAX Formula

[dcunningham27]

Hi there, 

I've used this forum a great deal and got an awesome DAX formula to calculate Net Working Days (see below). It works great when I have both a "Created Date" and "First CC Update" value. But when the "First CC Update" is blank, the formula doesn't work... I would hope that the formula can just return a Blank value instead. How can I modify my current DAX formula?

So the end result I'm looking for is:

Lead 1     Tuesday, January 1, 2019             Friday, March 15, 2019          54 days 0 hours 0 minutes
Lead 2     Wednesday, January 2, 2019

Lead 3     Wednesday, February 6, 2019     Sunday, February 16, 2019     8 days 0 hours 0 minutes

[Anonymous]

@dcunningham27  -

To check whether a value is blank, you can use the following pattern:

 IF(
    ISBLANK(<Value to Check>), 
    BLANK(),
    <Go get your value! 🙂 >
)

In this particular case, beginning with the RETURN line:

RETURN

 IF(
    ISBLANK(Calendar2), 
    BLANK(),
    COUNTX........
)

[dcunningham27]

@Anonymous I'm getting this error below?

[Anonymous]

@dcunningham27  - Oops! I didn't notice Calendar2 was a table variable. You can substitute

ISBLANK(Calendar2) 

with 

ISBLANK(MAX(HD[First CC Update]))

[dcunningham27]

Ahh 😞 So the formula works, but Power BI still doesn't like the "Blank" First CC Update value. So therefore, won't show it. See below.

[Anonymous]

@dcunningham27  - You could do the IF(ISBLANK check at the very beginning of the measure. If it's blank, don't do anything else.

[dcunningham27]

Where would I put the IF(ISBLANK check exactly? Before the Variables...?

Also, if it doesn't do anything else, would it still bring in the NetDays for the columns that have both dates...?

[Anonymous]

Yes, before the variables.

Yes, it would bring back a value, because this is calculated individually for each cell.

[dcunningham27]

@Anonymous 

Will it help if I try to attach the file?

I've also pasted the formula below. Not quite sure where to put the ISBLANK as it's throwing an error.

__NetWorkDaysHoursMinutes =

VAR Calendar1 = CALENDAR(MAX(HD[Created Date]),MAX(HD[First CC Update]))

VAR Calendar2 = ADDCOLUMNS(Calendar1,"WeekDay",WEEKDAY([Date],2))

RETURN

IF(ISBLANK(MAX(HD[First CC Update])), BLANK(), COUNTX(FILTER(Calendar2,[WeekDay]<6),[Date]) & " Days " &

HOUR(MOD(MAX(HD[First CC Update]) - MAX(HD[Created Date]),1)) & " Hours " &

MINUTE(MOD(MAX(HD[First CC Update]) - MAX(HD[Created Date]),1)) & " Minutes"

[Anonymous]

@dcunningham27  - 

Did you try it like this? :

__NetWorkDaysHoursMinutes =
IF(
	ISBLANK(MAX(HD[First CC Update])), 
	BLANK(),
	VAR Calendar1 = CALENDAR(MAX(HD[Created Date]),MAX(HD[First CC Update]))
	VAR Calendar2 = ADDCOLUMNS(Calendar1,"WeekDay",WEEKDAY([Date],2))
	RETURN
	 	COUNTX(FILTER(Calendar2,[WeekDay]<6),[Date]) & " Days " &
		HOUR(MOD(MAX(HD[First CC Update]) - MAX(HD[Created Date]),1)) & " Hours " &
		MINUTE(MOD(MAX(HD[First CC Update]) - MAX(HD[Created Date]),1)) & " Minutes"
)

[dcunningham27]

@Anonymous The formula works but it still gives me the below (see screenshot). It eliminates "Lead 2" row.

[Anonymous · Accepted solution]

@dcunningham27  - 

1. Select your visual

2. Right-click the date column and click "Show Items with no data".

Hope this helps,

Nathan

[dcunningham27]

You are freakin awesome @Anonymous. SUCH A LIFESAVER! THANK YOU VERY MUCH!!!!!