Calculating the Number of Working Days in Power BI | Custom NETWORKDAYS Function using Power Query

How to calculate total working days in Power BI?
In this video I show you how to calculate the number of working days in with a custom function-NETWORKDAYS in Power Query. If you are looking for a quick solution just copy it into a blank query and use it. If you want to learn how to write it yourself then watch the whole video till the end and learn Power Query...
Enjoy!

--------------------------------
📊 TRAININGS 📊
---------------------------------

---------------------------------
⏱️ TIMESTAMPS ⏱️
---------------------------------
0:00 Intro
0:38 Using the Custom NETWORKDAYS() Function
3:54 Writing the M Function NETWORKDAYS()
14:04 End

---------------------------------
😍 JOIN 😍
----------------------------------

---------------------------------
👇 CHECK THIS OUT! 👇
---------------------------------

* Above are affiliate links, which means at no additional cost to you, if you make a purchase using these links we will receive a small commission. It supports us and helps us to continue making more How to Power BI videos!

Thanks for being a part of this channel and all your support! 💪 🙏

#HowToPowerBI​ #PowerBI​ #DataTraining​
#powerbidesktop​ #powerbitraining​ #powerbideveloper​ #DAX

[EricMammen-sj]

Hi, Love the content! I used this about a year ago, but now we have NETWORKDAYS in PowerBI. Of course, now I'm trying to create a due date where I have a start date, number of days and want to find the due date. (So just like WORKDAY in Excel). Any chance you have a solution for this? Thanks

[FH_EAP]

Hi! Thank you for the useful tutorials! So helpful! Can you provide an update of this video now that PowerBI has rolled out its new NETWORKDAYS function in the July 2022 version? Excited to see your take on it! Thank you!

[AK-ljze]

You've nailed it. Most efficient way of getting things done. Just one small correction though
List.Dates = List.Dates(StartDate,
+1 at the end gives the right no of days B/W two dates excl Public Holidays & Wknd

[DonovanBisset]

I can’t find the custom code for this video?

[rachellim]

thanks for the video !!! Is there also possible to get ' negative output' for case when actual date is achieved earlier than its target date ?

[gregheath]

I do not get the same number of working days as the standard excel function.

Here is my function:
(StartDate as date, EndDate as date) as number =>
let
ListDates = List.Dates(StartDate, Number.From(EndDate-StartDate), #duration(1, 0, 0, 0)),
RemoveWeekends = List.Select(ListDates, each Date.DayOfWeek(_, Day.Monday) < 5),
CountDays = List.Count(RemoveWeekends)
in
CountDays

If I pass Start Date = 3/1/2020, 3/31/2020 the function returns 21 week days but March has 22 week days.

[dag]

The first function works great. How would I then remove a list of Holidays from the Workdays?

[suletopcu]

On question. How can we translate this formula into a conditional form? If some data does not have a closed date, if we want it to calculate from today() instead of closed date? Thank you very much!

[NPSingh-yi]

Hi Bas, I am one of your followers. I cannot see network days code which you suggested to copy and paste. Request you to please share it again

[jrgomez]

This is very helpful. What if it's date-time instead of just dates? I'm getting integers in total working days.

[AndyJones-te]

in my data I have start dates that are before the end date so the count is a minus (this is expected in the data set). This gives me an expression error. The 'count' argument is out of range as its a minus number. any ways around this so it returns the minus number ?

[deepside]

Great Video - Any suggestions on how to add business days to a start date? (and then exclude public holidays etc. too)

[roydramirezv]

Great video, would any body tell me why this formula works fine in my pc but in the pc of my partner doest't work?, the error says "the list appear is not complete" but the list of holidays is fine and was transformed as a list, I don't know if there are a mistake

[lilianaacosta]

Excellent solution! Just what I was looking for. Clear and concise.

[DuncanLon]

thank you for this video! In the end, you say that sometimes it's better to perform calculations in DAX, sometimes in the Query Editor. Do you have a video to explain how you decide where to perform the calculations? To me, it's still a bit random.

[danieviljoen]

Hi Bas, how can we create the list of the dates that we count?

[metchau]

I put in the function but the calculation on working days in a month for some months is off by 8 hours. Trying to figure out how to correct this.

[AliAlSayer]

I did it with Dax much easier than this

[jerrydellasala]

I only found your channel recently, but subscribed shortly after because the videos have been great. However, and I only mention this to help, your file downloads are the WORST I've ever seen! No way to search, no way to sort, and IF you're lucky the name of the file can be searched from the Browser FIND. Please find a better way!

[RobertSmith-pfox]

I am sorry but version with holidays does not work