topic Re: Date Calculation is blank if one of the dates blank in DAX Commands and Tips

Date Calculation is blank if one of the dates blank

common763 — Thu, 12 Sep 2024 19:25:48 GMT

I am getting a "Expressions that yeild variant data-type cannot be used to define calculated columns" error" if I change the 0 to "". I know this is simple but need help. I have the below calculated column.

Days btw dates = if(ISBLANK(DATE1,0, if(ISBLANK(DATE2),0,NETWORKDAYS(DATE1,DATE2)))

Right now it is displaying 0 if the DATE1 or DATE2 is blank. I want it to display "" or nothing if one or both of the dates are missing. Can someone help me tweak this calculated column to do so if one of the dates or both are blank?

Re: Date Calculation is blank if one of the dates blank

Dangar332 — Thu, 12 Sep 2024 20:10:06 GMT

Hi, common763

Try below measure

Days btw dates= if( ISBLANK(DATE1), 0, if( ISBLANK(DATE2), 0, NETWORKDAYS(DATE1,DATE2)))

Days btw dates = if( ISBLANK(DATE1), "", if( ISBLANK(DATE2), "", convert( NETWORKDAYS(DATE1,DATE2),STRING) ) )

Re: Date Calculation is blank if one of the dates blank

common763 — Thu, 12 Sep 2024 20:13:54 GMT

thanks. this works. Appreciate it.

Re: Date Calculation is blank if one of the dates blank

common763 — Fri, 13 Sep 2024 11:15:30 GMT

My head wasnt working yesterday...all i had to do was this below...I was putting "" instead of Blank()

Days btw dates = if(ISBLANK(DATE1,0, if(ISBLANK(DATE2),0,NETWORKDAYS(DATE1,DATE2)))

Days btw dates = if(ISBLANK(DATE1,BLANK(), if(ISBLANK(DATE2),BLANK(),NETWORKDAYS(DATE1,DATE2)))