Networkdays function with blank start dates

[Elisa112]

Hi Experts 

I am using the network days function :

Meeting date diff= NETWORKDAYS('Meetings'[1st Meeting],'Meetings'[2nd Meeting])-1

However some of the 1st meetings in the column are blank so the output become "36789".

How can I format the function to ignore blank start dates.  

Thanks in advance

[Ray_Minds]

In Power BI, you can handle blank values in your measure or calculated column by using the IF and ISBLANK functions:

1.  ISBLANK('Meetings'[1st Meeting]): Determines if the 1st Meeting column contains a blank value.
2.  IF statement: If the 1st Meeting is blank, the formula outputs BLANK(). Otherwise, it computes the difference using the NETWORKDAYS function.
3. Subtracting 1: Adjusts the result by subtracting 1 from the calculated value.
   
   

Did I answer your question? Mark my post as a solution!

[Elisa112]

@MFelix @Kedar_Pande Thanks for your help, I actually used conditional formatting to hide the incorrect values from appearing

[Kedar_Pande]

@Elisa112 

Calculated column

Meeting date diff = 
IF(
ISBLANK('Meetings'[1st Meeting]), 
BLANK(), 
NETWORKDAYS('Meetings'[1st Meeting], 'Meetings'[2nd Meeting]) - 1
)

💌If this helped, a Kudos 👍 or Solution mark would be great! 🎉
Cheers,
Kedar
Connect on LinkedIn

[MFelix]

Hi @Elisa112 ,

From the syntax I believe you are creating a column you can try something similar to this:

Meeting date diff=  IF('Meetings'[1st Meeting]) <> BLANK(), NETWORKDAYS('Meetings'[1st Meeting],'Meetings'[2nd Meeting])-1)

[Elisa112]

@MFelix Thank you, yes I am using  calculated columns, I tried your function, it works but does not show the records with a blank start date, which I still need as there are other subsequent dates which need to be visible for the record.

Any Ideas?

Thanks in advance

[MFelix]

In the first post you refer you want to ignore blank start dates, it seems like what you are writing in this last post is not matching that.

Can you please elaborate on what are the possible results with a example.

[Elisa112]

@MFelix although I want to ignore any blanks in the 1st meeting column, I do need the dates for the 2nd and 3rd meeting for the customer, see example below of the output I need

Cust ID   1st Meeting   2nd Meeting   Day Diff 1-2   3rd Meeting  Day Diff 2-3

001         blank             1 Jan 2024          blank          5 Jan              4

002         blank             blank                   blank         7 Jan              blank

This is a summarised table, the original meeting table is very large and the meetings,  have been ranked, therefore the 3rd meeting is the most recent. 

Is there a way to show null if there is no date for the first meeting but still include the record in the table ?

Thank you in advance

[MFelix · Accepted solution]

Hi @Elisa112 ,

Do you mean on the visualization level? You just need to select show items with no values for the column you want to have that information: