Bug using NETWORKDAYS

[EaglesTony]

Hi,

  I am trying to get the difference between a Start and End Date to complete an item, excluding weekends.

  I have a Start Date of 10/23/2021 and End Date of 10/24/2021, this should be 1 day. I also have a Start Date of 11/12/2022 and End Date of 11/12/2022, this should be 0 day.

  I have:

DaysToCompleteExcludeWeekends = NETWORKDAYS(TimeInStatus[StartDate], TimeInStatus[EndDate]) - 1

  However for the first example it is giving me -1 for both examples above.

[Anonymous · Accepted solution]

Hi @EaglesTony ,

In the case of the formula, the hidden <weekend> parameter is the default, and the formula determines that Saturday and Sunday will not be calculated.

NETWORKDAYS function (DAX) - DAX | Microsoft Learn

For the first record, the start date is Saturday and the end date is Sunday. There is a one day difference between these two dates, but this day is on Sunday, so it will not be counted, so it returns 0, then subtracts 1 to return -1.

Also for the second record, the difference between the two dates is 0 days, so it returns 0, and then subtracts 1 to return -1.

Best Regards,
Gao
Community Support Team

If there is any post helps, then please consider Accept it as the solution  to help the other members find it more quickly. If I misunderstand your needs or you still have problems on it, please feel free to let us know. Thanks a lot!

How to get your questions answered quickly --  How to provide sample data in the Power BI Forum

[DT5577]

Did this work? I do not think the Networkdays works as intended. Please advise. 

[EaglesTony]

I was thinking about this and the psuedo logic would be along these lines, but not sure how to code it in DAX:

If Start Date and End Date are both NOT on a weekend use NetworkDays
If Start Date AND End Date are both on a weekend use DateDiff
If Start Date is on a weekend and End Date is not on a weekend use DateDiff
If Start Date is NOT on a weekend and End Date IS on a weekend use DateDiff

[DT5577]

It seems that Networkdays has a bug. I have included holidays table to skip those days as well.