topic Re: Bug using NETWORKDAYS in DAX Commands and Tips

Bug using NETWORKDAYS

EaglesTony — Tue, 16 May 2023 15:18:10 GMT

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.

Re: Bug using NETWORKDAYS

EaglesTony — Tue, 16 May 2023 15:25:21 GMT

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

Re: Bug using NETWORKDAYS

Anonymous — Thu, 18 May 2023 02:05:06 GMT

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

Re: Bug using NETWORKDAYS

DT5577 — Mon, 18 Nov 2024 14:57:35 GMT

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

Re: Bug using NETWORKDAYS

DT5577 — Mon, 18 Nov 2024 15:04:26 GMT

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