I've watched a few videos before finding yours looking for this exact formula. Whilst the other videos may have got there as well, they weren't explaining what each part did, rather just bombarding us with do this, does that! Than you for explaining what each part does so I can now amend to cover my own use. Star! Subscribed 🙂
You've nailed it. Most efficient way of getting things done. Just one small correction though List.Dates = List.Dates(StartDate, Number.From(EndDate-StartDate)+1 +1 at the end gives the right no of days B/W two dates excl Public Holidays & Wknd
Thank you very much for this video. I have been working on a solution to this problem for weeks. I have watched a lot of training videos and you definitely have a talent for teaching.
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!
Thank you very much for providing such a useful function and a great way to explain it. It works perfect for me and now i am your proud subscriber too :)
Thanks, I have used it and it helps a lot, however i have to add one more condition where we shud check if there's any Month Start in between weeks, in such case the iteration shud start with the month start 😅
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 ?
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
Bro I have come across a specific issue from this Custom function. If Start Date or End Date falling on Holiday or Weekend count is not excluded. Please guide me.
@@HowtoPowerBI one thing: I am just wondering why you get the right result in column "Tot No Days excl Weekends 2" at minute 12:54 before you added "Holidays as list" in your custom function "NETWORKDAYS". I first had to add this parameter "Holidays as list" in my function, then it worked. Before I got an error saying that there was a third parameter expected but only 2 found... something like this. I did it in Excel Power Query, not Power BI. Maybe that's the reason
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!
Hi - how about - if I also want to find the difference(duration) by working hours? for instance - I have working hours as 0900 to 1700. And I wish to calculate the duration, in hours, from say - StartDATETIME as Friday, dd/mm/yyyy 15:35:40 to EndDATETIME as Monday, dd/mm/yyyy 10:15:35? The duration must only consider the business working hrs difference?
you would need to extend the logic a bit - (1) you can multiply by 8 for all days except the start and end date (2) for the start and end date you would need to calculate separately the hours and add it to the result of (1)
Hi! I have projects that overlap dates and would like to show the total # of days employees have work planned in a year? I have the duration of projects from start to end, but I'm STRUGGLING to figure out how to exclude the days where projects overlap. Do you how I would write a measure to do this? Thank you for all the vids and helping us pbi newbs🙏🏼
Hi, I have learnt So many things from your video. The way of explaining is so nice and easy to understand. Here instead of removing holiday and counting, how to remove for previlaged leave example:- let there are two E1and E2,E1 start date is 1-1-2020 and end date is 6-1-2020 and E2 start date 2-1-2020 and end date is 5-1-202, where E1 takes leave on 1-1-2020 and 4-1-2020 and E2 takes leave on 6-1-2020, then number of workday for E1 is 4(work day-no of leave) and number of workday for E2 is 4 days because the leave he has taken does not come in his work day, E2 leave comes under workday of E1 it should not affect on that because E1 has taken only two leave and its come under his workday therfore E1 workday is 6-2=4 how to count this one.
Great Video! If I import the exact same code for the holidays it takes ages until it loads the data in. Before I just hat removed the weekends and it worked great and fast. Any idea why the data load takes so lang after I typed in the holiday function?
Adding 1 day to the number of days between start and end corrected the issue and the PowerQuery function output matches the Excel function output. (StartDate as date, EndDate as date) as number => let ListDates = List.Dates(StartDate, Number.From(EndDate-StartDate) +1 , #duration(1, 0, 0, 0)), RemoveWeekends = List.Select(ListDates, each Date.DayOfWeek(_,Day.Monday) < 5), CountDays = List.Count(RemoveWeekends) in CountDays
This was so helpful! Thank you!!! So my issue is I need to know how many work days it has been since today. I have never used M so I’m not sure the best way to do this. I can make it work by creating a custom column with today’s date and then using that as the end date to pass in to the function, but it seems like there would be a better way, right? Thank you so much!
you can adjust the function so that it takes today as the end date. Replace each reference to the enddate in the function with DateTime.Date(DateTime.LocalNow()) . Hope this helps 🙂
Great video, great delivery, very informative! I do have a question however. While my Power query results match your results perfectly, they don’t always match the original NETWORKDAYS function in Excel. At 11:00 of your video in row 9 you shows 16 days for your start and end dates; However, if you use the function in Excel, the result is 17 days. Can you please address this discrepancy. (This is before you address holidays, by the way.)
I have the same question. I'm not sure if I am articulating it right but it seems like the function is counting the difference is days and not the true count of days.
Hey, Thanks for the amazing video, But I got an Error "Expression.Error: The 'increment' argument is out of range. Details: 1.00:00:00" Could you help me with that please
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.
Awesome as always... learning something new is incredible My Query: how to apply a logic if we have multiple countries with various holidays. How to create custom function?
thx!! 🙂 you can expand the logic and use multiple holiday lists. You then check with an if statement what the country is and dependent on that subtract the holidays in the corresponding list for that country.
How can I calculate work hour on Power BI? For example: date and time start "09/15/2021 07:00 a.m" and date and time process finished "10/15/2021 08:30 a.m". The duration (result) of this process between this date and time is = 11:30 hours (without count Full 24 hours, just count work time available). The period of job starts at 7:00 a.m and the end is 5:00 p.m. I need Just count Thank you!
Thank You So much.....I have used it but some how if my end date is on Saturday of Feb'2021 (I tried with only 2months data), it is including Saturday also in to my Network Day. Rest it's working all correct. Any suggestion
Hey, great stuff! Is there a way to stop the calculation once a certain criteria is met? I want to count the number of days from the start date but I want the counter to stop once the order has been shipped. Current calculator continues the count even if the order has been shipped out.
Thank you. This was tremendously useful. I am creating my date table in a dataflow, so I need to use m language (Power Query). If I have my holidays in a table (coming from an Excel spreadsheet), how do I create a list?
Love it great contribution, thanks! I have a list with over 100K lines which take for ever to calculate as the list of dates to be excluded adds up to probably about 10K lines. Any hint on how this could be optimized furhter?
Great stuff! How to custom select the day of the week which I want to exclude as the holiday? For example if I only want to exclude Friday & my week starts on Sunday
Hi, i need to calculate time duration between 2 columns having date and time in each column (just working day's which means excluding weekends). Could you help?. Many thanks in advance.
@@HowtoPowerBI Good Day - Please could you assist - I found this video on Calculating Business Hours with Dax -> th-cam.com/video/_O8o6Hxuyjk/w-d-xo.html - Is there an easier way to do this ?
Hi Bas, thank you so much for this video. 1 question how to add if condition along with this. As an example, if the status is closed then calculate network days.
Hey! This is exactly what I've been trying to do. So helpful to come across this video. Thank you! Question though: I'm calculating the working day of each month. So my start date is the start date of the month and the end date is the date colum. On weekends I noticed it is still calculating a number... It's just a repeat. For example: 1/1/23 holiday counted as 1 1/2/23 weekend counted as 1 1/3/23 actual workday 1 - counted as 1 Is there a better way I could display this so my end users know which one is the true workday? So I end up with 3, 1's 1/17/23
Hello Bas, really good explanation. I have a further question: What if my EndDate is earlier than my StartDate? I want also to calculate the negative ranges in order to get delay figures. In my case if I simulate the function with StartDate > EndDate, I receive the following error message: "Expression.Error: The 'increment' argument is out of range. Details: 1.00:00:00" Is it possible to also calculate negative ranges with this custom function? Would be really nice, if you could answer.
you could add an if statement to check if the enddate is before the startdate and multiply by -1 if it is = (StartDate as date, EndDate as date) as number => let DateList = if EndDate < StartDate then List.Dates(StartDate, Number.From(StartDate - EndDate), #duration(1, 0, 0, 0)) else List.Dates(StartDate, Number.From(EndDate - StartDate), #duration(1, 0, 0, 0)), RemoveWeekends = List.Select(DateList, each Date.DayOfWeek(_, Day.Monday) < 5), CountDays = List.Count(RemoveWeekends) in -CountDays
@@HowtoPowerBI Hi Bas, thanks for the video. The statement works but then all values returns in negative. In my database StartDate can be >, = or < EndDate. How this function coulf return - 0 or +?
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
Well I wanted this to work. I followed the instructions but when trying to use the "NetworkDays" function in a column I just get "Failed to resolve name 'NetworkDays'. It is not a valid table, variable or function name."
Hi Bas, i am using this exact method but am getting some numbers and some error values. both the start date and the end date have some nulls in the rows and that is where the error is coming. my requirement is the eventually calculate the average days between the start and end date but with the errors it is not allowing me to do so. would you know a solution for this please? thanks!!
Hi Amrita, you could use the try / otherwise statement -> = Table.AddColumn(#"Added Custom1", "networksdays", each try NetworkDays([Start Date],[End Date], Holidays) otherwise null)
you can create two measures, one that sums up the all days column and one that sums up the workdays column. Then you can use a slicer to switch between the two. I will make a video on how to do that. In the meantime I think this video from curbal answers how to set it up th-cam.com/video/gYbGNeYD4OY/w-d-xo.html
Very informative. Thanks for explaining it so nicely. Best video so far. However, what to do if there are records where the End Date isn't there? For eg when we are looking at sales data with both closed and open opportunities and the open ones won't have any Closed Date as such. In this scenario, the calculated column throws an error. Any way to handle that? Thanks in advance!
you can use try ... otherwise .. and return null instead of error (StartDate as date, EndDate as date, optional Holidays as list) => let DateList = List.Dates(StartDate, Number.From(EndDate - StartDate)+1, #duration(1, 0, 0, 0)), RemoveWeekends = List.Select(DateList, each Date.DayOfWeek(_, Day.Monday) < 5), //RemoveHolidays = List.RemoveItems(RemoveWeekends, Holidays), CountDays = List.Count(RemoveWeekends) in try CountDays otherwise null
@@HowtoPowerBI This is great! But even adding "try CountDays otherwise null" the query still gives an error and I can apply it. Any other suggestion? Thank you in advance!
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.
Adding 1 day to the number of days between start and end corrected the issue and the PowerQuery function output matches the Excel function output. (StartDate as date, EndDate as date) as number => let ListDates = List.Dates(StartDate, Number.From(EndDate-StartDate)+1, #duration(1, 0, 0, 0)), RemoveWeekends = List.Select(ListDates, each Date.DayOfWeek(_,Day.Monday) < 5), CountDays = List.Count(RemoveWeekends) in CountDays
Good morning. Thanks in advance for this great video. It was such as helpful for me. But, how can I add extra hours of shifts to function? Thanks in advance
This is awesome! It’s so bizarre that PowerBI doesn’t have this natively, especially when its relevance is already recognised within Excel 🤷♂️ Got a question for you: I have a dataset which generates 20k rows each month. The time variables are: Opened (datetime), Closed (datetime) One of the calculations is Working Days between these datestamps, returned as a whole number. I’m keen to maintain a lean and efficient dataset and bringing 20k datestamp rows will surely bloat the cardinality! Is it possible, using a function like this, to calculate the working days within PowerQuery, bring this custom column in and then discard the original Open and Closed columns? So, instead of a bunch of dates and times, I can bring the metric in (which is a primary feed into an MTTR analytic) ?
If you don't need the open or close times for your model then sure, you could definitely do that. Another way you can reduce the cardinality and keep the dimension is to split them apart. make a single date and a single time column then remove the original open/close datetime column.
@@NewUnit13 That’s a great shout! I hadn’t thought of that at all. I mean I could probably do with keeping one date dimension (Closed) as a month identifier, as it’d save me extracting it from the file name string. Some great food for thought - many thanks for your reply and guidance 😀👍
This is great. But how do you extend the functions for a third argument, for region, as weekends and public holidays aren't the same globally, but can vary by region, e.g. Australia, vs U.A.E.?
The formula from the video already contains that as a third argument. Just refer add a list with the holidays you need eg Australia and then refer to that in the function -> NetworkDays([Start Date],[End Date], Holidays_Australia)
@@sachin.tandon You can adapt this line to the rule you need for the weekends -> RemoveWeekends = List.Select(ListDates, each Date.DayOfWeek(_,Day.Monday) < 5),
Thanks fpr share it! but there are i dont understand. if my start date is July 1 and EndDate is July 7.... why is returning 4?? Considering that July 1 is thusrday and July 7 is Wednesday ... between these 2 days are 5 not 4
it is not counting the start date itself .. just add 1 after Number.From(EndDate - StartDate) and that will fix it (StartDate as date, optional EndDate as date, optional Holidays as list) => let DateList = List.Dates(StartDate, Number.From(EndDate - StartDate)+1, #duration(1, 0, 0, 0)), RemoveWeekends = List.Select(DateList, each Date.DayOfWeek(_, Day.Monday) < 5), //RemoveHolidays = List.RemoveItems(RemoveWeekends, Holidays), CountDays = List.Count(RemoveWeekends) in try CountDays otherwise null
there is a download in the description from which you can download the file with the function to copy. (StartDate as date, EndDate as date, Holidays as list) as number => let DateList = List.Dates(StartDate, Number.From(EndDate - StartDate), #duration(1, 0, 0, 0)), RemoveWeekends = List.Select(DateList, each Date.DayOfWeek(_, Day.Monday) < 5), RemoveHolidays = List.RemoveItems(RemoveWeekends, Holidays), CountDays = List.Count(RemoveHolidays) in CountDays
Hi I tried entering the same code using the same steps but when I am trying to test using invoke it gives an error as An error occurred in the " query. Expression. Error: we cannot convert a value of type function to type List details Value =[Function] Type =[Type] Could you please help me fix this
I've watched a few videos before finding yours looking for this exact formula. Whilst the other videos may have got there as well, they weren't explaining what each part did, rather just bombarding us with do this, does that! Than you for explaining what each part does so I can now amend to cover my own use. Star! Subscribed 🙂
You've nailed it. Most efficient way of getting things done. Just one small correction though
List.Dates = List.Dates(StartDate, Number.From(EndDate-StartDate)+1
+1 at the end gives the right no of days B/W two dates excl Public Holidays & Wknd
Yes. This gets the correct results. Thanks.
@@logiman No need to +1, there are other videos that use +1, they are so misleading, this is so far the only correct calculation I have seen!!
Thank you , i advise viewers watch from starting , dont skip .. it is really works ..
thanks Riyaz for watching! Happy to hear you got it to work 🙂
Excellent solution! Just what I was looking for. Clear and concise.
Thanks!
thanks Hakan for your support!
I ve been looking for this for 2 days now. thanks man
Wow... My 2hrs of Google search cane to an end.... 👏
Thank you very much for this video. I have been working on a solution to this problem for weeks. I have watched a lot of training videos and you definitely have a talent for teaching.
Very helpful to my work, very clear, concise and precise discussion, you are a genius...…..thanks for putting this for us
You're very welcome! thx for watching!!! 😀
Exactly what I was looking for. Thank you!
woohoo 🎉
Thank you, it is very helpful because I find out that it can do it power query.
Glad it helped 😊
Great stuff! Really appreciated this video. I've seen similar solves but this helped me understand the how behind the function.
thanks for the video !!! Is there also possible to get ' negative output' for case when actual date is achieved earlier than its target date ?
Outstanding Video!! Keep them coming.
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!
Great video!
Thanks Sam!
Obrigado por compartilhar conhecimento!
Fique na paz!
Thanks for sharing knowledge!
Stay in peace!
Really good explanation! Very clear. Thanks a lot
Oh!!! With music in the background, learning productivity is better :)
oh nooo - my newer vids don't have any music 😄😆 - thx for watchn Alexandr
@@HowtoPowerBI area for improvement, even your video content is awesome! :)))
I will try this however weekends in my country Is different. You’re awesome anyways
Thank you very much for providing such a useful function and a great way to explain it. It works perfect for me and now i am your proud subscriber too :)
great to hear it was helpful, thx for your sub! 😃
The first function works great. How would I then remove a list of Holidays from the Workdays?
Did you find out
The function is not supported in DirectQuery mode? Am looking for an alternative to the out of the box networkdays that works for directquery.
Thanks, I have used it and it helps a lot, however i have to add one more condition where we shud check if there's any Month Start in between weeks, in such case the iteration shud start with the month start 😅
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 ?
I can’t find the custom code for this video?
Me too
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
Thank you and your beard man. Keep sharing
:):) haha 😂 will do!!
Awesome Video Thanks for your great help
Glad it helped! thx Gopinath
Bro I have come across a specific issue from this Custom function. If Start Date or End Date falling on Holiday or Weekend count is not excluded. Please guide me.
Thank you so much man!
Just great!!!
thank you for watching Clemens!!! 😀
@@HowtoPowerBI one thing: I am just wondering why you get the right result in column "Tot No Days excl Weekends 2" at minute 12:54 before you added "Holidays as list" in your custom function "NETWORKDAYS". I first had to add this parameter "Holidays as list" in my function, then it worked. Before I got an error saying that there was a third parameter expected but only 2 found... something like this. I did it in Excel Power Query, not Power BI. Maybe that's the reason
Sooooo helpful. Thank you 🙂
Incredible! I've been looking for a quick solution to this for ages.
Thank you!
if I only knew, then I would have uploaded it earlier 😁😉 ... happy it helped!
this was brilliant thank you!!
thanks Amrita! too much credit! 😊😃
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!
Um, I can't find the code you've used to copy into a blank query, mentioned in the description area?
This was so helpful 👍 thank you
awesome, happy it helped!! 😃
Thank you sooo much it was very helpfull and nice learning video.
Glad it was helpful! 😀
Thank you from Shanghai
thx for watching Jason! greetings from Düsseldorf 😉
Great solution, I would love to see how display the measure in graphs.
thanks 🙂there is no measure in this video though
Hi - how about - if I also want to find the difference(duration) by working hours? for instance - I have working hours as 0900 to 1700. And I wish to calculate the duration, in hours, from say - StartDATETIME as Friday, dd/mm/yyyy 15:35:40 to EndDATETIME as Monday, dd/mm/yyyy 10:15:35? The duration must only consider the business working hrs difference?
you would need to extend the logic a bit -
(1) you can multiply by 8 for all days except the start and end date
(2) for the start and end date you would need to calculate separately the hours and add it to the result of (1)
@@HowtoPowerBI -The logic you gave is clear :), but the question is how to program the above two step logic in Power Query. :)
Cannot find the link in the description for the M script. Someone please point it to me. Thanks
Hi! I have projects that overlap dates and would like to show the total # of days employees have work planned in a year? I have the duration of projects from start to end, but I'm STRUGGLING to figure out how to exclude the days where projects overlap. Do you how I would write a measure to do this? Thank you for all the vids and helping us pbi newbs🙏🏼
Thank you. ❤
You're welcome 😊
Hi, I have learnt So many things from your video. The way of explaining is so nice and easy to understand. Here instead of removing holiday and counting, how to remove for previlaged leave example:- let there are two E1and E2,E1 start date is 1-1-2020 and end date is 6-1-2020 and E2 start date 2-1-2020 and end date is 5-1-202, where E1 takes leave on 1-1-2020 and 4-1-2020 and E2 takes leave on 6-1-2020, then number of workday for E1 is 4(work day-no of leave) and number of workday for E2 is 4 days because the leave he has taken does not come in his work day, E2 leave comes under workday of E1 it should not affect on that because E1 has taken only two leave and its come under his workday therfore E1 workday is 6-2=4 how to count this one.
Great Video! If I import the exact same code for the holidays it takes ages until it loads the data in. Before I just hat removed the weekends and it worked great and fast. Any idea why the data load takes so lang after I typed in the holiday function?
Do you have a similar video that calculates a Due Date based on 30 working days excluding weekends and holidays?
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.
Adding 1 day to the number of days between start and end corrected the issue and the PowerQuery function output matches the Excel function output.
(StartDate as date, EndDate as date) as number =>
let
ListDates = List.Dates(StartDate, Number.From(EndDate-StartDate) +1 , #duration(1, 0, 0, 0)),
RemoveWeekends = List.Select(ListDates, each Date.DayOfWeek(_,Day.Monday) < 5),
CountDays = List.Count(RemoveWeekends)
in
CountDays
@@HowtoPowerBI Thanks! This works great!
Thank you!!! You are so cool :D
Wonderful, i can use this. Thank you Bas
nice! great to hear!
This was so helpful! Thank you!!! So my issue is I need to know how many work days it has been since today. I have never used M so I’m not sure the best way to do this. I can make it work by creating a custom column with today’s date and then using that as the end date to pass in to the function, but it seems like there would be a better way, right? Thank you so much!
you can adjust the function so that it takes today as the end date. Replace each reference to the enddate in the function with DateTime.Date(DateTime.LocalNow()) . Hope this helps 🙂
Thank you!
@@lukeandchitraswygard8567 does it work, if yes can you share the code
Great video, great delivery, very informative! I do have a question however. While my Power query results match your results perfectly, they don’t always match the original NETWORKDAYS function in Excel. At 11:00 of your video in row 9 you shows 16 days for your start and end dates; However, if you use the function in Excel, the result is 17 days. Can you please address this discrepancy. (This is before you address holidays, by the way.)
I have the same question. I'm not sure if I am articulating it right but it seems like the function is counting the difference is days and not the true count of days.
solution is mentioned in below comment on this if you havent already seen it.
How can I do this for datetime inputs and output a decimal for the duration??
Hey, Thanks for the amazing video, But I got an Error "Expression.Error: The 'increment' argument is out of range.
Details:
1.00:00:00" Could you help me with that please
Hi, I am unable to see the description section. Can somebody please help, where can i see the description section
how can I have it use "today" as end date if the end date column is empty?
What if the start date or end date is sometimes blank? Is there a condition I can use in that case?
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.
This is very helpful. What if it's date-time instead of just dates? I'm getting integers in total working days.
Awesome as always... learning something new is incredible
My Query: how to apply a logic if we have multiple countries with various holidays. How to create custom function?
thx!! 🙂 you can expand the logic and use multiple holiday lists. You then check with an if statement what the country is and dependent on that subtract the holidays in the corresponding list for that country.
@@HowtoPowerBI Thanks Bas. Will try and keep you posted 🙂
How can I calculate work hour on Power BI?
For example: date and time start "09/15/2021 07:00 a.m" and date and time process finished "10/15/2021 08:30 a.m". The duration (result) of this process between this date and time is = 11:30 hours (without count Full 24 hours, just count work time available).
The period of job starts at 7:00 a.m and the end is 5:00 p.m.
I need Just count
Thank you!
Thank You So much.....I have used it but some how if my end date is on Saturday of Feb'2021 (I tried with only 2months data), it is including Saturday also in to my Network Day. Rest it's working all correct. Any suggestion
Hey, great stuff! Is there a way to stop the calculation once a certain criteria is met?
I want to count the number of days from the start date but I want the counter to stop once the order has been shipped. Current calculator continues the count even if the order has been shipped out.
Thank you. This was tremendously useful. I am creating my date table in a dataflow, so I need to use m language (Power Query). If I have my holidays in a table (coming from an Excel spreadsheet), how do I create a list?
I could not find the dax script for this query!
it is not dax, it is M power query ... see download link in the description
I am not able to utilize this function on visual. Any help here please?
thanks for the title ... landed here because of the title of this video
great to hear 🙂
Love it great contribution, thanks! I have a list with over 100K lines which take for ever to calculate as the list of dates to be excluded adds up to probably about 10K lines. Any hint on how this could be optimized furhter?
Great stuff!
How to custom select the day of the week which I want to exclude as the holiday?
For example if I only want to exclude Friday
& my week starts on Sunday
I cannot get the remove holidays to work??
Hello Bass, thanks for this video it is very helpful but can a do with Power Query on how to calculate Sick Leave please. Thanks Again
Hi Bas, how can we create the list of the dates that we count?
Hi, i need to calculate time duration between 2 columns having date and time in each column (just working day's which means excluding weekends). Could you help?. Many thanks in advance.
EXCELLENT
Where can I find a good holiday dates csv?
what code should we write if end date needs to be the systems date
for the end date you can then take DateTime.Date(DateTime.LocalNow())
Hi
Thank you for the video but I wanted to know if it is possible to consider working hours while calculating the difference
think so, haven't tried though - sounds like a nice idea for a follow-up video 🙂
@@HowtoPowerBI Good Day - Please could you assist - I found this video on Calculating Business Hours with Dax -> th-cam.com/video/_O8o6Hxuyjk/w-d-xo.html - Is there an easier way to do this ?
Hi Bas, thank you so much for this video. 1 question how to add if condition along with this.
As an example, if the status is closed then calculate network days.
Hey! This is exactly what I've been trying to do. So helpful to come across this video. Thank you!
Question though: I'm calculating the working day of each month. So my start date is the start date of the month and the end date is the date colum. On weekends I noticed it is still calculating a number... It's just a repeat. For example:
1/1/23 holiday counted as 1
1/2/23 weekend counted as 1
1/3/23 actual workday 1 - counted as 1
Is there a better way I could display this so my end users know which one is the true workday?
So I end up with 3, 1's
1/17/23
Thanks a lot .. need to add 1 to the formula as it results 1 less
Hello Bas, really good explanation. I have a further question: What if my EndDate is earlier than my StartDate? I want also to calculate the negative ranges in order to get delay figures. In my case if I simulate the function with StartDate > EndDate, I receive the following error message: "Expression.Error: The 'increment' argument is out of range.
Details: 1.00:00:00" Is it possible to also calculate negative ranges with this custom function? Would be really nice, if you could answer.
you could add an if statement to check if the enddate is before the startdate and multiply by -1 if it is
= (StartDate as date, EndDate as date) as number =>
let
DateList =
if EndDate < StartDate then
List.Dates(StartDate, Number.From(StartDate - EndDate), #duration(1, 0, 0, 0)) else
List.Dates(StartDate, Number.From(EndDate - StartDate), #duration(1, 0, 0, 0)),
RemoveWeekends = List.Select(DateList, each Date.DayOfWeek(_, Day.Monday) < 5),
CountDays = List.Count(RemoveWeekends)
in
-CountDays
@@HowtoPowerBI Hi Bas, thanks for the video. The statement works but then all values returns in negative. In my database StartDate can be >, = or < EndDate. How this function coulf return - 0 or +?
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
can you clear cache and see if this fixed it?
Hi Bas, can you please advise how can we add the time into the customs function? Thanks
#time(hour as number, minute as number, second as number) as time .. for example, #time(1, 30, 0) would be 1 hour 30 min
Well I wanted this to work. I followed the instructions but when trying to use the "NetworkDays" function in a column I just get "Failed to resolve name 'NetworkDays'. It is not a valid table, variable or function name."
Oh I needed to do the custom column in the power query, got it.
how to add if statement here? I want to skip all these rows that have an empty start or end date
please help
HI I am not able to find the function to copy it.
You can download the file from the link i the description. Open it, go to transform data, query editor :)
@@HowtoPowerBI Sorry The download link is taking to the video again and I don't see any file download there.
@@HowtoPowerBI Got it. Thanks for the Quick reply.
Hi Bas, i am using this exact method but am getting some numbers and some error values. both the start date and the end date have some nulls in the rows and that is where the error is coming. my requirement is the eventually calculate the average days between the start and end date but with the errors it is not allowing me to do so. would you know a solution for this please? thanks!!
Hi Amrita, you could use the try / otherwise statement -> = Table.AddColumn(#"Added Custom1", "networksdays", each try NetworkDays([Start Date],[End Date], Holidays) otherwise null)
@@HowtoPowerBI thank you this is perfect!! works!
@How to Power BI First of all thank you and it works. But can I somehow use this as a filter to switch between workings days and all days?
you can create two measures, one that sums up the all days column and one that sums up the workdays column. Then you can use a slicer to switch between the two. I will make a video on how to do that. In the meantime I think this video from curbal answers how to set it up th-cam.com/video/gYbGNeYD4OY/w-d-xo.html
@@HowtoPowerBI Thank you for your quick response! I looking forward to your next video.
Very informative. Thanks for explaining it so nicely. Best video so far.
However, what to do if there are records where the End Date isn't there? For eg when we are looking at sales data with both closed and open opportunities and the open ones won't have any Closed Date as such. In this scenario, the calculated column throws an error. Any way to handle that?
Thanks in advance!
thx Ritika!! 😀
you can use try ... otherwise .. and return null instead of error
(StartDate as date, EndDate as date, optional Holidays as list) =>
let
DateList = List.Dates(StartDate, Number.From(EndDate - StartDate)+1, #duration(1, 0, 0, 0)),
RemoveWeekends = List.Select(DateList, each Date.DayOfWeek(_, Day.Monday) < 5),
//RemoveHolidays = List.RemoveItems(RemoveWeekends, Holidays),
CountDays = List.Count(RemoveWeekends)
in
try CountDays otherwise null
@@HowtoPowerBI This is great! But even adding "try CountDays otherwise null" the query still gives an error and I can apply it. Any other suggestion? Thank you in advance!
Nice video, is there anyway that both start and end days is also counted?
thx Mahdi - you can add plus 1 -> List.Dates = List.Dates(StartDate, Number.From(EndDate-StartDate)+1
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.
Adding 1 day to the number of days between start and end corrected the issue and the PowerQuery function output matches the Excel function output.
(StartDate as date, EndDate as date) as number =>
let
ListDates = List.Dates(StartDate, Number.From(EndDate-StartDate)+1, #duration(1, 0, 0, 0)),
RemoveWeekends = List.Select(ListDates, each Date.DayOfWeek(_,Day.Monday) < 5),
CountDays = List.Count(RemoveWeekends)
in
CountDays
thanks for bringing it up and sharing your solution!
Good morning. Thanks in advance for this great video. It was such as helpful for me. But, how can I add extra hours of shifts to function? Thanks in advance
This is awesome! It’s so bizarre that PowerBI doesn’t have this natively, especially when its relevance is already recognised within Excel 🤷♂️
Got a question for you: I have a dataset which generates 20k rows each month. The time variables are: Opened (datetime), Closed (datetime) One of the calculations is Working Days between these datestamps, returned as a whole number.
I’m keen to maintain a lean and efficient dataset and bringing 20k datestamp rows will surely bloat the cardinality! Is it possible, using a function like this, to calculate the working days within PowerQuery, bring this custom column in and then discard the original Open and Closed columns?
So, instead of a bunch of dates and times, I can bring the metric in (which is a primary feed into an MTTR analytic) ?
If you don't need the open or close times for your model then sure, you could definitely do that. Another way you can reduce the cardinality and keep the dimension is to split them apart. make a single date and a single time column then remove the original open/close datetime column.
@@NewUnit13 That’s a great shout! I hadn’t thought of that at all. I mean I could probably do with keeping one date dimension (Closed) as a month identifier, as it’d save me extracting it from the file name string.
Some great food for thought - many thanks for your reply and guidance 😀👍
Edward makes a good point, split the date time. Keep in mind that you can also use DAX to return the net working days
Great Video - Any suggestions on how to add business days to a start date? (and then exclude public holidays etc. too)
This is great. But how do you extend the functions for a third argument, for region, as weekends and public holidays aren't the same globally, but can vary by region, e.g. Australia, vs U.A.E.?
The formula from the video already contains that as a third argument. Just refer add a list with the holidays you need eg Australia and then refer to that in the function -> NetworkDays([Start Date],[End Date], Holidays_Australia)
@@HowtoPowerBI What if weekends also vary by region - e.g. Australia vs U.A.E. - how do you accommodate for both W/Es and Holidays?
@@sachin.tandon You can adapt this line to the rule you need for the weekends -> RemoveWeekends = List.Select(ListDates, each Date.DayOfWeek(_,Day.Monday) < 5),
How can you make this work for TODAY() instead of and end date? Thanks
replace end date with todays date Date.From(DateTime.LocalNow())
Thanks fpr share it!
but there are i dont understand. if my start date is July 1 and EndDate is July 7.... why is returning 4??
Considering that July 1 is thusrday and July 7 is Wednesday ... between these 2 days are 5 not 4
it is not counting the start date itself .. just add 1 after Number.From(EndDate - StartDate) and that will fix it
(StartDate as date, optional EndDate as date, optional Holidays as list) =>
let
DateList = List.Dates(StartDate, Number.From(EndDate - StartDate)+1, #duration(1, 0, 0, 0)),
RemoveWeekends = List.Select(DateList, each Date.DayOfWeek(_, Day.Monday) < 5),
//RemoveHolidays = List.RemoveItems(RemoveWeekends, Holidays),
CountDays = List.Count(RemoveWeekends)
in
try CountDays otherwise null
I do not see the function anywhere for me to copy
there is a download in the description from which you can download the file with the function to copy.
(StartDate as date, EndDate as date, Holidays as list) as number =>
let
DateList = List.Dates(StartDate, Number.From(EndDate - StartDate), #duration(1, 0, 0, 0)),
RemoveWeekends = List.Select(DateList, each Date.DayOfWeek(_, Day.Monday) < 5),
RemoveHolidays = List.RemoveItems(RemoveWeekends, Holidays),
CountDays = List.Count(RemoveHolidays)
in
CountDays
it throws error when we have start and end dates in date/time format.
How to fix that???
you can wrap the dates in a Date.From function - adjust the references to start / end date like this --> Date.From(StartDate)
Hi I tried entering the same code using the same steps but when I am trying to test using invoke it gives an error as
An error occurred in the " query. Expression. Error: we cannot convert a value of type function to type List details
Value =[Function]
Type =[Type]
Could you please help me fix this
I think I figured it out and this code is working fine for me thanks a lot for the Video
aaah I'm too slow 😉 happy to hear your already have it figured out
@@HowtoPowerBI thanks a lot the code really helped me