Return Working Days in Power BI using DAX

hello and welcome back in this video we're going to be taking a look at how to return the number of working days from our date table so stay tuned so recently I was working on site with a customer and this question came up and they didn't really have a good date table yet right they had a pretty generic date table that we created and they wanted to know how can we just count the number of days from our date uh table just for working days and they also wanted to be able to include some pretty basic holidays in that right so you know obviously just to count the number of days from the date table pretty simple operation I just want to do account rows of the date table that returns all the days that's assuming that you have a well-built date table that has one date for every day of the year and so if you filter that down to 2009 you're you're going to get 365 days now if you want to return the total days for you know work days or days of the week then you might try to do calculate count rows from the date table where you know the days in the week are day numbers of the week are two through six right so maybe an in clause or multiple ore conditions whatever way you want to do it pretty easy calculation though but is there an easier way to do it and so it came to my attention one of the people there was actually oh there's this I saw read about this function the other day it's Network days can we use that and I was like ah this looks interesting I'll take a look at it and I just typed it out real quick and it really did kind of give us exactly what we wanted so I was like you know what I didn't know about this function so probably a lot of people don't let's do a quick YouTube video on it so that's what we're going to do on my report very basic very simple report I have the total number of days that are in my date table right here 2,191 and if I look at that measure that I built it's exactly what you would expect this is the count rows from my date table the way I would have generally built a working days or a days of the week weekday calculation is I would have taken this measure and I'd create a new one i' say calculate total days where you know we'd have a filter on there where are the days in the week or Tuesday through or Monday through Friday or 1 through six whatever your day number is and I'd build a another measure it take a couple of seconds no big deal but another way to do this this would be with network days right so on my date table here I'm going to go ahead and build a new measure so I'll just right click on the date table build a new measure and we'll take a look at this one and then in a moment I'm going to show you the documentation so this will be called working days all right we're just going to call this one here working days I'll even go ahead and just zoom in there we go and I'm going to build a couple of quick variables because we're going to need these right so I'm going to build a variable here and the first first variable I'm going to build is going to be the first date that we're going to pass into this measure so first date equals and I'm just going to grab the minimum date from the current filter context then I'll build another measure and this is going to be last date and this is going to be the maximum date from the current filter context right so if somebody puts a filter on the year 2009 I would get January 1st of 2009 and then I would get the maximum date from that date range which is December 31st of 2009 and that's what I'm going to pass into my function and then I'll come down here to the bottom and I'm going to say return and then I'm going to write my function and we're going to use Network days right so I'm going to write this completely and entirely different than I would have generally considered writing this in the past and so I'm going to come in and I'm going to say Network days and when you type this out it tells me right here in the the tool tip it needs a start date it needs an end date and then there's two optional parameters and we know they're optional because they have these little little square brackets around them right that means it is optional I think it's the same way in Excel as well if you write expressions there and so we can also pass in this is what's cool you can pass in your own list of weekends so even though I say weekend is Saturday and Sunday you might say well for me it's actually Friday and Saturday so you can pass in your own list and I'll show you that here in the documentation in just a moment so I'm going to pass in my my parameters my first one is going to be first date my second one is going to be my last date and that's really all that's required right at this point we're kind of done unless we want to change maybe the default weekend or if we want to add a list of what was the other thing there it was holidays so we'll come back to that in just a moment but if I hit enter and then of course I'm going to go ahead and format this right here that has my new measure right here called working days I'm going to grab working days drag it over here drop it on this card visual that was already prepared for us and there we go so I get 1565 days now if I click on my year and I click 2009 this is counting the number of rows in my date table for the year 2009 if I go to 2008 you see it changes because it was a leap year and this is changing as well this tells me the number of working days so the number is clearly different right it's smaller so what's it doing is it excluding holidays that are known is it excluding weekends what is it doing well the default Behavior as I've learned is it just excludes by default the weekends it doesn't make any assumptions on holidays okay so it excludes Saturdays and Sundays automatically that's what I've seen as I've explored this now a way that we can validate that of course is I could say for 2009 261 right so I could go to my date table I can filter down my date table to calendar year I might still have it filtered I do I already have it filtered here and what you'll see is that I've filtered it down right here to the year 20 9 right so I've already filtered this table down to the year 2009 and then I've also filtered the table down over here on the left to the day number of week 2 through six which is as you can see Monday through Friday and the reason I did that is because at the very bottom of this table this is a great way to validate your code just a very simple way at the very bottom of this table it's going to tell me what I done so my table originated with 2,191 rows but after the filter that I've applied there are 261 rows so this table currently has 261 rows in it right for the year 2009 for Monday through Friday so it matches exactly what my measure is returning and this is when I only exclude right when I only exclude weekends so there's no holidays being kind of counted here now let's take a look at the documentation real quick because the documentation is important and if I pull up Network days in the Microsoft learn documentation this will kind of give us some pretty good information about what's going on so first of all at the top it tells you it Returns the number of whole work days between two dates so we choose the dates it looks at the date table it returns all the dates that are work days Monday through Friday by default um weekend days and days specified as holidays are not considered work days okay so we can exclude those here's the basic syntax we already saw that before it tells you a start date and an end date what those parameters represent and then it gives you this weekend property what does this mean this is kind of cool I like this a lot because it gives you very quick and easy flexibility without having to you know go through and specify so for example if I only wanted to Omit Sunday and say Sunday is our weekend but we consider Monday through Saturday working days that optional parameter for weekends I would just put the value of 11 and then it would only ignore Sunday so it would count count Saturdays as a working day but you can go through here and you say you know what Saturday only is a weekend so you put 17 instead um what I normally do is I'll come in here and well you could just do Saturday or Sunday so you could do one um or omitted what does that mean or omitted that means if you don't put something there it assumes Saturday and Sunday that's what it means so if you just want to go straight up normal every day what is the weekend you're going to stick one in there right so these are the different options and I think it's cool I like this a lot that it gives you these different options so that's what we got so if I flip back over here and I go back over to my measure let's take a look at what happens if we make a quick change now remember we skipped the optional parameters right but if I come in here and I add a spot to put in the first optional parameter which is weekend and I do something like S I think seven was Sunday or Saturday I think it was Sundays if I do seven and I hit enter watch what happens to my working days right here well it's the same I forgot maybe seven was a good one hold on oh I think seven was Friday and Saturday yeah it was Friday and Saturday I thought I was grabbing Sunday here so 11 um yeah so that still is kind of excluding two days which still Falls within that range of kind of matching 261 right so one would be Saturday or Sunday let's just go with Sunday only my fault we're going to go back and we're going to do Sunday which is 11 I was thinking 17 which was Saturday so I I messed that up 313 right so now we went from 365 to 261 to 313 so it's only excluding Sundays so Monday through Saturdays are considered weekday so this is pretty cool the next one and this one is a little bit trickier and so I want to kind of dive into this a little bit is the option to add in holidays and when I first looked at this I was kind of like how do I do this right I knew that probably the best way to do this was to have a better date table to have a column on my date table that said is this a holay day or not because that's the way I would always write my Dax anytime I'm writing Dax helping a customer solve a problem it's always going to be I'm going to be changing the filter context or adding to the current filter context and I'm going to do that with a column that's on a table right I'm going to filter by that column so i' say calculate count rows from my date table where is holiday equals or is yeah is holiday is holiday equals false don't count those don't count the ones that are false or yeah or count the ones that are false don't count the ones that are true don't count the holidays right so I would add a flag on that table so the question is how do I pass that in this doesn't want to let me just pass in the date column from my date table where it equals that um because you have to have a one column table so let's take a look at that together real quick if we come over here and go a little bit further down the example that it shows here which I don't love in the documentation so sometimes you got to kind of work around that a little bit is it says hey you can just provide a list that's with these curly brackets and you can list out all your dates now that's not again that's not going to be the most ideal solution my my suggestion would be build those build this logic into your date table so it's reusable in a lot of different places not you're having to copy this code everywhere you know that I'm just reiterating it for spec you know specificity right just for specific sake I'm just re referencing you don't want to do this but you could right we could go in there and we could say hey I want to add in two holidays that we we don't we just don't want those to be you know we're not going to count those as working days right so I could come in here and of course December 25th and the United States maybe in a lot of places we say hey that's we're not working on December 25th right so I could come in here and uh say date I could type in date here and then type in the year so we say 2009 I'm just going to stick with one year obviously you type this in for every year and then I say the month is going to be 12 and then we're going to go with 25 right and so that's one uh I need to close that parentheses there now I still have my list open so I could throw in another day so I'm going to say you know what I'm going to throw in another date here and I think it is Valentine's Day I think is on what February 14th maybe so let's do that 2009 now I don't know if these are holiday or weekends so if these are on the weekends might mess up my number just a little bit because what I want to happen when I get to the end is for this to turn into 311 I'm adding two holidays right for 2009 so we go with 02 and then we add one4 close that up so what I've done if you look at this here is I've added a list of holidays that I'm passing in to this function so that it doesn't count those days right so I hit enter it should drop this number at least a little bit and it did we were at 313 now we're at 311 now again I don't love this option if I had 10 years in my table I'd have to type 1225 10 times right and I have to create this massive list it's better to put that into your date table build an if condition um and have an is holiday column but how do we use that column so I'm going to show you right now now if I go to my date table you'll notice all the way over here on the right I've created this is holiday using some very funky Dax no big deal I just wanted to create a couple of columns here or create a couple of rows that were true so I've created some holidays all right so I have some falses and I have some trues right so what I want to do is any row that is true I want to consider them holidays and I don't want to count them they're not a working day so if we go back into Network days that's what we're going to do now so let's build a variable instead of just typing it straight into the code I'm going to build a variable here and I'm going to say holidays right this is my list of holidays that I want to pass in to this parameter and so the holidays that I'm going to pass in is let's do we're going to calculate table and I'll go ahead and go down to the next line calculate table we're going to bring back a uh distinct list of dates from my date table here so we'll do that and then I'm going to add a filter and so the filter I want to add is going to be date and then is holiday equals true right because that's what it was it's a true false um data type and so that's going to return a DAT now there is a way to test this I always like to test my code that's one of the things that makes variables great so before I even put this into Network days I'm going to kind of comment that code out with control forward slash and I'm going to count the number of days that are being returned by the holidays table and I think if I remember correctly it's supposed to be nine there's nine days that I specified so this is telling me that if I count these days from this table here I'm doing a count rows on my filtered table my calculate table expression it's nine days so this is what I'm going to pass in right here I'm going to say exclude those nine days so now we should go again those might be some of those weekends so might not exactly subtract nine but we see the numbers going to go down so you build a column on your table it has the holidays in it and this is how you pass it in so here we go let's let's get rid of that we're going to go back to our expression here I'll make it a little bit bigger control SL to uncomment that code and then right here in this list I'm going to say hey let's pass in my list of holidays right so I'm passing those nine days in and I hit enter and what we should see here is 304 pretty good because originally we were at 313 if you remember we were at 313 and then I put two days in there manually that dropped us to 31 and then I got rid of that and I replaced it so 313 to 304 is the 9 days so it's not counting those holidays and this is how you do it very cool very cool Dax function super easy to use I don't know that I would use it a whole lot to be honest with you because obviously I've been running Dax for a long time I kind of have my own way of how I would have done this but I do think it's cool I think it has some very practical use especially if I had a date table already and that date table was Saturdays and Sundays but I wanted to do some other analysis very quickly and I wanted to do Friday and Saturday and see how that might impact some numbers maybe I would use this right now that I know it's here instead of having to build another new column in my date table I could just plug this right in that's it that's all I got for you today I hope you enjoyed this video I hope you learned something new I hope you find it exciting and we'll see you in the next one

Channel: Pragmatic Works

Views: 3,327

Keywords: power bi, power bi networkdays, solutions abroad, data analytics, dax

Id: 6KXryi4O-2E

Length: 16min 28sec (988 seconds)

Published: Thu Oct 19 2023