Calculating the Number of Working Days in Power BI | Custom NETWORKDAYS Function using Power Query
Вставка
- Опубліковано 13 лип 2024
- 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!
Download file here datatraining.io/powerbi-how-to
--------------------------------
📊 TRAININGS 📊
---------------------------------
Power BI Design 4 Weeks Transformation Program my.datatraining.io/pages/powe...
Power BI Essentials datatraining.io/powerbilearni...
Business User Training datatraining.io/powerbi-busin...
For Custom Trainings and Consulting email directly support@datatraining.io
---------------------------------
⏱️ TIMESTAMPS ⏱️
---------------------------------
0:00 Intro
0:38 Using the Custom NETWORKDAYS() Function
3:54 Writing the M Function NETWORKDAYS()
14:04 End
---------------------------------
😍 JOIN 😍
----------------------------------
Join bit.ly/4b453bi
Subscribe bit.ly/31MnQGO
Insta / howtopowerbi
LinkedIn / basdohmen
TikTok / how.to.power.bi
X / howtopowerbi
fb / howtopowerbi
Threads www.threads.net/@howtopowerbi
Newsletter datatraining.io/newsletter
---------------------------------
👇 CHECK THIS OUT! 👇
---------------------------------
💻 My gear amzn.to/47F21Yc
📚 Power BI books MUST READ! amzn.to/3tUfFcj
💡 General books I recommend amzn.to/48YNo33
🎶 Music for my videos www.epidemicsound.com/referra...
🚀 For growing on UA-cam: www.tubebuddy.com/bas
🏄 Stuff I use daily amzn.to/3HqfMQ2
* 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 - Наука та технологія
Excellent solution! Just what I was looking for. Clear and concise.
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.
Great stuff! Really appreciated this video. I've seen similar solves but this helped me understand the how behind the function.
I ve been looking for this for 2 days now. thanks man
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 🙂
Exactly what I was looking for. Thank you!
woohoo 🎉
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 🙂
Really good explanation! Very clear. Thanks a lot
Obrigado por compartilhar conhecimento!
Fique na paz!
Thanks for sharing knowledge!
Stay in peace!
Outstanding Video!! Keep them coming.
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!!
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!!! 😀
Wow... My 2hrs of Google search cane to an end.... 👏
Thank you so much man!
Sooooo helpful. 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 :)
great to hear it was helpful, thx for your sub! 😃
this was brilliant thank you!!
thanks Amrita! too much credit! 😊😃
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 sooo much it was very helpfull and nice learning video.
Glad it was helpful! 😀
This was so helpful 👍 thank you
awesome, happy it helped!! 😃
Thank you!!! You are so cool :D
Wonderful, i can use this. Thank you Bas
nice! great to hear!
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!
Thanks Sam!
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!
EXCELLENT
Thank you. ❤
You're welcome 😊
I will try this however weekends in my country Is different. You’re awesome anyways
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
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?
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.
Thanks!
thanks Hakan for your support!
thanks for the video !!! Is there also possible to get ' negative output' for case when actual date is achieved earlier than its target date ?
Thank you and your beard man. Keep sharing
:):) haha 😂 will do!!
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 😅
Great Video - Any suggestions on how to add business days to a start date? (and then exclude public holidays etc. too)
Thank you from Shanghai
thx for watching Jason! greetings from Düsseldorf 😉
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?
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
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! :)))
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?
Great solution, I would love to see how display the measure in graphs.
thanks 🙂there is no measure in this video though
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.
This is very helpful. What if it's date-time instead of just dates? I'm getting integers in total working days.
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.
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🙏🏼
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
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.
@@HowtoPowerBI Thanks Bas. Will try and keep you posted 🙂
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
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
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
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
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
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!
@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 ua-cam.com/video/gYbGNeYD4OY/v-deo.html
@@HowtoPowerBI Thank you for your quick response! I looking forward to your next video.
Hi, Thanks for the video, worked great. How can I amend the query to include the start date as the first day. Currently, if the period is a single day the result is 0, I'd like this to display 1 etc . Thanks in advance
Hi Andrew, just add +1 🙂
(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
Thanks a lot .. need to add 1 to the formula as it results 1 less
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.
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.
Do you have a similar video that calculates a Due Date based on 30 working days excluding weekends and holidays?
thanks for the title ... landed here because of the title of this video
great to hear 🙂
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
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
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 -> ua-cam.com/video/_O8o6Hxuyjk/v-deo.html - Is there an easier way to do this ?
How can I do this for datetime inputs and output a decimal for the duration??
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),
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
Hi, I am from India. Thanks for giving me apportunity to learn something advance. I have query related this topic. I have 3 different sheets, 1st sheet has unique EMP id and target of one day. 2nd sheet has EMP id, Date, and last 2 year production count. 3rd sheet has holidaylist. Now I want to check production count weekly with weekly target excluding weekend and holidays or monthly production count with monthly target excluding weekend and holidays and same for yearly.
So can you please help me out?
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 +?
The function is not supported in DirectQuery mode? Am looking for an alternative to the out of the box networkdays that works for directquery.
Um, I can't find the code you've used to copy into a blank query, mentioned in the description area?
Cannot find the link in the description for the M script. Someone please point it to me. Thanks
What if the start date or end date is sometimes blank? Is there a condition I can use in that case?
Hi, I am unable to see the description section. Can somebody please help, where can i see the description section
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?
Amazing. Loved the way you explain. However, I have a challenge. I want to give a solution in power bi, where my user will select a date from a calendar picker and want to calculate below calculation -
Example below:
I have two date columns in my table (Start Date and End Date).
I want to give a calendar picker to my user in visualization page. Based on the date selected by my user, I want to calculate two columns:
First column: "How many days completed?"
Formula: Selected Date - Start Date + 1
Second column: "How many days to GO" ?
Formula: End Date - Selected Date + 1
Is it possible ? Initially, I thought to keep dates as parameter. But we don't have DATE datatype. Please help.
Hi Santosh - you should look into a dax solution not power query as you would have to write a measure to calculate based on what the user selected
I can’t find the custom code for this video?
Me too
Where can I find a good holiday dates csv?
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 - 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. :)
I am not able to utilize this function on visual. Any help here please?
how can I have it use "today" as end date if the end date column is empty?
Just a side note, this method only work with date that are date only, it does not work with time and minutes as well. Also use the split column function for date and time, if you use the transform to date only the calculation for holidays will be wrong. I done all this in Power Query, don't know if this is the same in Power Bi
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!
I cannot get the remove holidays to work??
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 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!
Can you show me where the M code to copy?
How can you make this work for TODAY() instead of and end date? Thanks
replace end date with todays date Date.From(DateTime.LocalNow())
Hi!
I need to calculate networking days, I'm using:
(StartDate as date, EndDate as date) 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),
CountDays = List.Count(RemoveWeekends)
in
CountDays
And selecting between 6/1/21 to 6/30/21 the result is 21, and it should be 22
(StartDate as date, EndDate as date) as number =>
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),
CountDays = List.Count(RemoveWeekends)
in
CountDays
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 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!
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
how to add if statement here? I want to skip all these rows that have an empty start or end date
please help
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.
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!
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
I tried to follow step-by-step but get the following:
Also selected a holidays list to avoid this, to no avail. Any leads how to fix this?
Expression.Error: 2 arguments were passed to a function which expects 3.
Details:
Pattern=
Arguments=[List]
you can take holidays out of the function
(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
alternatively,
provide a holiday list as input
or make it an optional argument
@@HowtoPowerBI will try that. Thank you!
I did it just like the vedio taught, but i got the error:
An error occurred in the ‘’ query. Expression.Error: We cannot convert a value of type Function to type List.
Details:
Value=[Function]
Type=[Type]
I don't know which step is wrong. Anyone can help?
sounds like a data type issue, .. could you copy your code here?