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:
Solved! Go to 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
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
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