Complex Issue in Power BI using =NetworkDays()

[Jays_D]

Hi All,

I am novice in Power BI but loving the learning journey. I have come across a problem I am not sure how to approach.

Problem
I have 4 start dates and 4 end dates (see below table):

Start Date Column

Team 1 Start

Team 2 Start

Team 3 Start

Team 4 Start

End Date Column

Team 1 End

Team 2 End

Team 3 End

Team 4 End

I want a new column that will do the following (using =Networkdays):

• Find which column has Start Date (e.g. [Team 2 Start] contains the date)
• THEN
• See if the corresponding End Date is also captured ([Team 2 End])
• If End Date exists - calculate # of Network days between the 2 dates
• ELSE
• Use Today() as the End Date
• Calculate # of network days between the 2 dates (with 2nd date being Today())

I have been able to work out how to do this between 2 specified date columns but unsure how to get it find which column contains a start date and then it's corresponding end date column.
This is what I am using to achieve that:

=NETWORKDAYS([Team 1 Start Date],IF([Team 1 End Date]= BLANK(),TODAY(),[Team 1 End Date]))

Apologies if I haven't been clear. Would really appreciate the guidance.

Thank you.

[skygold16]

I think it would be better if you unpivot your date columns and follow what @v-yanjiang-msft  suggested in the first response. 

[Jays_D]

Thanks for the response. The columns are not pivoted, these are actual columns in a Dataverse database that I cannot change unfortunately.

[v-yanjiang-msft]

Hi @Jays_D ,

According to your description, I create a sample to reproduce:

Here's my solution, create a calculated column.

Column =
IF (
    [Start Date Column] = BLANK (),
    BLANK (),
    NETWORKDAYS (
        [Start Date Column],
        IF ( [End Date Column] = BLANK (), TODAY (), [End Date Column] )
    )
)

Get the result:

I attach my sample below for your reference.

Best regards,

Community Support Team_yanjiang

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Jays_D]

Thank you ! This is great, however I am thinking maybe my description was not clear:

I have a different Start and End date column per team.

So for example:

Job No.	Team1 Start	Team 1 End	Team 2 Start	Team 2 End	Team 3 Start	Team 3 End
12345	01/05/2023	Today()	07/05/2023	09/05/2023	09/05/2023	Today()

Sincerely appreciate that you have responded, thank you again.

[v-yanjiang-msft]

Hi @Jays_D ,

Do you mean for each Job No, there are corresponding Team1 Start/End, Team2 Start/End, Team3 Start/End?

So for the example you support, what's the expected result? Could you please explain the calculate logic?

Best regards,

Community Support Team_yanjiang

[Jays_D]

That is correct. Sorry not sure why my table posted unformatted like that.

I want a calculated column that will do the following (using =Networkdays):

• Find which column has a Start Date (Check each of the 'Start' date columns and find the one that contains a Start Date. Only 1 column will have a start date. Example [Team 2 Start] has a date in it.
• THEN
• See if the corresponding End Date is also captured ( so if [Team 2 Start] contained the date, check [Team 2 End] for an End Date)
• If End Date exists, calculate # of Network days between the 2 dates
• ELSE
• If End Date does not exist, use Today() as the End Date and calculate # of network days between the start date and Today.