Forum Discussion

Jays_D's avatar
Jays_D
Advocate II
3 years ago

Complex Issue in Power BI using =NetworkDays()

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):   ...
Need Help
v-yanjiang-msft's avatar
v-yanjiang-msft
Community Support
3 years ago

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's avatar
    Jays_D
    Advocate II
    3 years ago

    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 StartTeam 1 EndTeam 2 StartTeam 2 EndTeam 3 StartTeam 3 End
    1234501/05/2023Today()07/05/202309/05/202309/05/2023Today()

     

    Sincerely appreciate that you have responded, thank you again.

    • v-yanjiang-msft's avatar
      v-yanjiang-msft
      Community Support
      3 years ago

      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's avatar
        Jays_D
        Advocate II
        3 years ago

        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.