Calculate business hours with DAX in Power BI

hi there this is Matt Ellington and in my video today I'm going to talk about this topic how do you calculate the total number of elapsed business hours between two date time columns and the use case might be if you have a help desk a ticket is opened at a particular time and then it's closed at some time in the future and you want to know how many business hours have elapsed during the time that that ticket has been open so let me start off by showing you some data which I've created so I have some sample helpdesk tickets here so these tickets were open this one on the first of January at 8:30 8 a.m. and it was closed on the same day at at 4:20 p.m. for 19 p.m. and here are the total number of elapsed hours but of course elapsed hours are not the same as business hours so this is the sample data that I'm going to use now what I'll do is I'll just show you a bit of an illustration of the problem because to solve this problem we need to break the problem into pieces so what I have here is a sample time line so these are days of the week so Sunday Monday Tuesday and so on Saturday Sunday Monday into the next week so let's talk about how these problems might manifest and what we need to do in order to solve the problem so these gray boxes here represent non-working hours and so basically if I put a line here that would be midnight on Sunday night and if I drew another line in here this would be midnight on Monday night and then what I've got here this darker area is representing the business hours so let's say for the sake of the argument it's 8:00 a.m. to 5:00 p.m. Monday to Friday okay so with that in mind let's have a look at a couple of scenarios so one scenario is that the job gets opened sometime during the day it doesn't matter which day it is but let's say sometimes during the day on a Monday so after the business start of day 8:00 a.m. and it gets finished sometime during the same day so if that was the scenario we would have to calculate the total time between two time periods the start and end time okay another scenario of course is that it could start before the start of day so before the business hour start maybe you've got some computer logging system where someone logs a ticket outside of your business hours it comes in at 3:00 a.m. in the morning and then in that case the job might be closed during the same day the job might be closed after the end of the business hours potentially and the job might be closed on a further day and in fact the job might be closed at some time in the future and so let's and let's take another sample here let's say the job starts some time during the day on a Monday and then it's finished some time during the day on a Friday well if we break this problem into pieces we need to work out how much time from the start to the end of the first day and then how much time from the beginning of the last day to the time of closure on the last day and then we need to add the number of complete days in between in which case they would be nine business hours each from 8:05 so it'd be the start day plus the finish day plus the four days in between of course the complication comes in when we have weekends and so we need to not count the weekend days as business hours and indeed a complication could come in if we had a public holiday on one of the days like a Monday and instead of counting the time on that Monday we would have to ignore that Monday as well this is the problem breaking down into its smallest number of pieces and that's the problem that we need to solve now you might be thinking should I solve this problem using power query or should I try and solve the problem using Dax and in actual fact in this case you can use both tools to solve the problem but let me tell you about the considerations that I took into account before making my decision on which of these tools or languages to use and so I asked myself do I need to leverage the data model to make this task easy so that means do I need to think about the relationships the filter propagation between and any measures all of these three things are part of the data model and can be leveraged and used when writing the Dax language if I don't need to use any of these things then power query is a great solution but in my case I've decided to do a calculated column using Dax because I do want to leverage the relationships and filter propagations particularly to allow me to access the working days and non-working days the public holidays I need to be able to filter the tables to be able to work out the number of working days and I also want to break the problem into pieces and using variables in Dax is a great way to break a complex formula into workable pieces and so for this reason on this occasion I've decided to use the Dax language okay so let's go back to my sample data that I've produced here I will solve this problem in power bi desktop but I'm going to prepare my sample data here in Excel so there's the help tickets that I showed you before and also now that we've had a look at the problem there's a couple of other bits of information they're going to be very useful so one bit of information is what are the business hours for a standard day so in that example I'm using I'm starting from a tool and working through five so they're the standard business hours per day and then the second thing that I would find useful would be to be able to load the public holidays and so I need these public holidays to know whether each day is a working day or a non working day so I just have some sample public holidays here from one of the states in Australia so let's go and set about loading this data and then we'll set about solving the problem now the first thing I'm going to do is create a calendar table and probably the easiest way to do this is using power query if you go to my website xbi comdata you that's the shortcut for my website short for accelerator bi of course so xbi dot-com today you and come here and with search on a reusable calendar table or power query calendar table I've written a blog article about how to create one of these you can come back and read this in your own time and this is a step-by-step guide on how to start literally with a single date and build a calendar table that will automatically update I take you through all those processes this is a power query solution so come back and have a look at this when you're ready but for now what I'm going to do is I'm going to copy this code and you're welcome to do this as well so I'm going to come here and toggle the raw code I think I can just now click copy to clipboard and I'll come back to power bi desktop and I'm going to come here and click transform data so this will load power query and I'm going to do a new source blank query and switch to the advanced editor and just paste the code that I've copied from my website in here and now I have a calendar table now in my calendar table you can come here and you can set the start date so the sample data that I'm using starts in 2020 so I'm just going to set the first of January 2020 as the start date and basically this calendar table works out what today's date is and sets the length of the calendar from there so there's the calendar table and I'm going to call this calendar okay so I'm going to go ahead and load the other three tables that I have in my Excel sample file so I'm going to do a new source it's an excel file and there's my sample data so I'm going to connect directly to that source and I'm simply going to connect to the tables not to the sheets in this case I'm going to select the tables because the tables are already structured with headers and so on it just makes the data a little bit easier and so I click okay connect to those three tables and load them up into power bi desktop as well now I definitely want to load this calendar table into my data model but the business hours table and the holidays table I'm going to use for other purposes in fact they do now I think about I do need this one so I will load that one but I'm going to set this one to do not load and what I need to do is I need to get into my calendar table I need to know is that a working day or a non working day and I need to take into account the public holidays so these public holidays are in a human readable form by year I'm just going to unfit other columns and in fact because my tickets only work from 2020 I'll leave the 2021 data in there but I'll just maybe put a filter on here is so this is actually a text field so let me go ahead and change that to a whole number and once they do that I can come here and do a number filter is greater than or equal to 2020 and then if I update this in the future with future years worth of public holidays that will work so here's all my holidays from 2020 and beyond I don't need this column is going to remove it and there's the date I'll call this my public holiday date it's good practice just to give good names to your columns all right so now I need to get this data into my calendar table and so this will just be a simple merge so I'm going to merge queries starting with my calendar table I'm going to merge it to my holidays based on the date a left outer join so I want everything from the calendar table and only the ones that match and there are six rows that match the reason I don't have matches into the future is that my calendar table currently only goes through to the current date which is sort of mid year so I'm gonna click OK with that and now I'm going to extract the name of the holiday I don't need the date because I already got the date in the date column and so now I have a column that tells me the name of the public holiday or null if it's not a public holiday in fact I'm going to go back here and remember I said that this calendar automatically updates based on today's date and so instead of allowing today's date to be calculated I'm just going to hard-code the end of year so I'm just going to say 31 twelve 2020 that's an Australian date format and I need to get rid of the equal sign if I'm going to put a date and there's the date and so now I've hard coded the calendar to end at the end of this calendar year in fact if given my public holidays go forward I'll put it at the end of next year okay so we come back here now and we've got the all the public holidays joined in for the next two years and now I'm going to create a working day column so I want a column that indicates is this a working day or not so this Thursday the second of January is a working day so I need to differentiate that between a Saturday Sunday and a public holiday the easiest way to do this is with the conditional columns and then add a column conditional column and my new column is going to be called working day and I'm going to say if now let's have a look at the logic here if the day of week is a 1 or a 7 then it's not a working day so I can just do that and I can make this as simple as I want maybe let me do it slightly differently I'll say if the day name equals Saturday or sat then I want 0 now I'm going to return 0 for a non working day and one for a working day and that's quite deliberate because it makes that column very easy to add up I can to add up the number of ones and work out how many working days do I have so the day name is equal to Saturday then give me zero I'll do another Clause if the day name equals some then give me zero and I'll add another clause if the holiday equals and I can say does not equal null in other words if it's not a no then it's a zero if it's not enough if it's a null it's a working day potentially depending if it's a weekend and if it's not an hour it's a public holiday otherwise it's a working day and so this should give me a column you get this some data validation at the top is very useful I can see exactly the data profile here if I click on this column you can see here there's 730 values no errors and and so this data is working correctly I'm going to turn that into a whole number and you can see here there are two distinct values so I know that there's no novels down here that's all working fine and now I've got my working day column I'm going to leave my holiday column there because that might be useful information as well okay let's have a look at the business hours so this all looks fine so I've got the business hours and the time I really want to change this to just the time so I extract the timestamp I don't need to know the date so we replace current now let's have a look at the help desk ticket so we've got the open date time in date time and the description and the elapsed hours and it's going to be much easier to split these into two columns so to do that I'm going to first of all click the first column add column and I'm just going to extract at the time and I'm going to call this I'm going to rename this start time I'm just going to do it directly up here in the formula bar just saves one step and do the same with the end time add column time only and I'm going to call this this end time now I've done that I can change the data types for these two to be date only do them one at a time so date and date and then I'll change these to be open date and end date right this is good practice to load the data it's also good practice to round off the seconds if they're not needed but for the sake of the exercise I'm just going to leave that so now we have our open date and time and end date and time I might call this start for consistency I'll call it start date and time okay so that's the data that I need so now I'm going to file close and apply and then let's move over to power bi and DACs okay so here I am in the model view and here are my tables I have my calendar table and I need to link my calendar table which is a dimension table or a lookup table to my tickets and I've actually got two date columns the start date and end dates so I'm going to link the start date to the calendar date and I'm also going to link the end date to the calendar date noting that I did the start date first so that's the active relationship and this one is inactive and I can reference the calendar table using the inactive relationship when needed the business hours table is actually a disconnected table I have no need to join that to anything else in fact it makes no sense to join it but I can use this to extract the information of the start of day time and the end of day time okay so here we are in power bi the data is loaded I'm here in the data view and I'm looking at the help desk tickets table I've just noticed that the elapsed hours has a very large number of decimal places it's not good practice to load precision at a level that you don't need I'm not going to come up here and make the change I am actually just briefly going to go back into the query editor and I'm going to use power query to round this off I'm actually using decimal hours in this case notice I can't see it here because it's basically been rounded to two decimal places but I'm going to transform that column and just going to round it to two decimal places this is a much better practice than loading unnecessary precision and this will definitely make your models smaller and faster and more performant over time so that's a good tip for you to take into account okay so I'm going to actually build a calculated column to solve this particular problem I want a new column that shows me the total business hours between the start date and time and the end date and time and the reason I'm using a calculated column for a few reasons one because I want to leverage the calendar table and the relationships to know which public holidays exist etc and also potentially this formula would be quite slow at run time and it would be better to pre calculate it as a calculated column and permanently store it in my model so that it's there when I use it so I'm going to create a new column and I'm going to call this new column business hours business hours open and now we need to step through the problem-solving process that reflects the PowerPoint document that I showed you before okay I'm going to cut and paste my my code across one step at a time but it's important to know that this is the way I actually solved this particular problem when I did it myself and now I'm going to use the Dax variable syntax variables and return in order to break the problem into pieces so this is the first formula that I'm going to write I need to know what is the start time for the business day and so I'll write this formula and then I'll return the variable and let's have a look and you can see that this is returning 8:00 a.m. as the start time so the way this formula works is first of all I put a filter on the business hours equal start and then I return the single value left in the business hours table if we have a look at the business hours table if I put a filter on start then there is only a single row in that time and selected value returns that particular value and if I change this to time only then that would give me the the total hours all right now I'm not going to bother about the format here because I'm actually not going to use it in this particular way so this tells me they're starting hours and so the next thing to do is to work out what the end of the business day is so I'm going to paste this formula I prepared before and so the business hours end same process and now I just check always check every part of the variable formula that I write before moving on to make sure that I've done it correctly so let me come back here and this is returning 5:00 p.m. so that's correct as well so the next thing I want to do is I'm going to work out what is the length of a business day so here's my next piece of the formula so my business hours per day is the business hours end time subtract the business hours start time this gives me the fraction of a day and then I times it by 24 to turn it into hours and I'm wrapping it in a value function by doing that it converts that date time format into a in this case to be a decimal number and just it doesn't it's not required I don't believe in Excel but in my experience it is required in power bi desktop and let me check the business hours per day just to make sure that that seems to be working as expected so nine hours is the average business day of course I could hard-code that number but I've actually put that into my table in my spreadsheet so that if at some stage I want to change the number of business hours in a day I can just change those start and end times and everything else will just flow through okay so just let me come back to my slide here my illustration so what I want to do for the first part of this problem is to work out how much time between the time that the ticket was opened through to the end of the first day so that's the portion I'm going to work on now and so to solve that I need to know what time is the ticket opened so I need to grab this starting time and so this starting time let's return that I'm doing this as a variable because it just makes it easier to be clear whether that's working correctly once again I'm not going to bother about formatting this but you can see it is correctly returning the starting time as being 8 38 and 24 seconds okay with this in mind I can now set about working out how much time has elapsed on the first day so I'm going to do another variable my first day elapsed time and I'm going to use the switch true construct and what switch true allows me to do is to put some quite complex nested if statements basically into a single function and then based on the rules that I said it lets me work out what number should be returned okay so the first thing I'm going to do is I want to know is this a working day because if it's not a working day then the first day elapsed time will be zero if the ticket comes in on a Sunday there is no business hours time on the first day and so I'm using the related function and I might just comment this out so that I can stop here and talk about what this related function does remember we're in the helpdesk tickets table and what the related function does is it allows you to leverage a relationship in this case it's the active relationship so I'm writing a new column in the help tickets table and what related says is go up through the active relationship and go to the single record in this case the calendar table run across to the working-day column and bring that number back and tell me what that is is it a one or a zero the best way to think about related is it's like vlookup and it leverages an existing relationship that you have in your table so let me come back over here and let's see what this returns first day elapsed time notice that I'm checking I haven't built out the rest of my switch statement it's returning one for the working days and it's not returning anything for the non-working day so it's saying so go and check if the working day zero then returns zero otherwise it's returning blank basically and so it's going up grabbing and it's telling me that there are zero business hours because these are non-working days now just looking at this I was just thrown for a second because I'm thinking why is the Wednesday giving me a nonworking day but of course it's Wednesday the 1st of January it's a public holiday and therefore it's a nonworking day and then the Saturday the 4th of January well the Saturday should be a nonworking day as well so maybe I've made a mistake let's go and have a look here so calendar table working day I'm getting ones for these Saturdays and Sunday so I've made an error in my formula now this is a genuine error that I made when I'm recording this video and it just goes to highlight why it is so important to break your problems down and solve them one step at a time so there's something wrong with my calendar table you may have picked that up and earlier on there must be something wrong in my business logic that I did for my calendar table so let's go ahead and have a look so here's my conditional column I can come here now what did I do let me have a look at this conditional column so I said if the day name is equal to Saturday then it's a zero and look what I've done here it's I've used the word Sat and Sun with the wrong column and so I need to change this to D D D is that D D D is son whoops that was wrong d D D son otherwise if I think it was the holiday is now so let's go ahead and look at that so now we can see we're getting some ones and I've got zeros here not there's definitely something still wrong so if the day Saturday or Sunday at zero if it's a public holiday equals null then it's actually a working day otherwise it's let me say does not equal null and it's a holiday otherwise it's working day let me try that okay so that looks better so we've got two non-working days there two non-working days there and there's a nonworking day for the holiday I clearly should have checked that before progressing but the good thing was because I broke the problem down into pieces I was able to identify that problem before moving on and this is a genuine example of how to solve these problems one step at a time making sure that every step is correct before moving forward and so now we've correctly showing the public holiday let me this is sorted I believe but let's just sorted ascending so non-working days non-working days and then Monday and so on okay so let's keep going inside this switch statement I'll do a new line and the next line of code I want to know is is the start time after the end of day because if the start time is after the end of the day we also need zero hours for the first day so let's have a look at that and so what we need to do is we need to find a working day so Monday the 6th of January is a working day the start time is after 5:00 p.m. so we're returning zero for that day so that piece is correct as well okay the next piece of the puzzle so continue to build this out put a new row so the next piece is what if the start time is before the start of day so if the start time is on or before 8:00 a.m. then if the number of hours on the first day will just be the total number of business hours that day which will be nine of course so let's just test that so notice how I've this is how I'm using the the total business hours of the day so I'm returning nine I'm not hard coding nine in here I'm setting that as a variable upfront so basically if the job starts before 8:00 a.m. in the morning we're getting nine business hours on the first day finally if none of those conditions are true then we want the business hours end versus the start time and so let's go ahead and have a look at this and now I need to change the formatting here because it's currently formatted as a whole number so it'll change that to a decimal number with a couple of decimal places and you can see here this one is showing 3.1 hours so this would be the last one so I want the business hours end of day subtract the start time and then times by 24 and I'm rounding it down to three decimal places so this one started close enough to 2:00 p.m. which is three hours and five minutes short at the end of the day and we're returning 3.1 hours so that all looks correct okay so back to the plan so here we are again so here's my sample let's say that the ticket is closed during a particular day let's say it's Tuesday the next thing I want to do is the exact opposite of the start so instead of recording from the start to the end of the first day I want to take from the closed time to the beginning of that day so I need to work out how much time was elapsed on the last day that the ticket was opened now this begs an interesting question what would happen if the ticket was closed on the same day I've just written a formula that calculates the total hours on the first day from the start time through to the end of the first day but what happens if the ticket is closed on the same day so I need to address that issue as well and so I need to check is the closed date on the same day as the open date so I need to know the closed date and the open date so I might come back and write some new variables for that so this will be the start date equals they help this ticket start date and the end date is the helpdesk ticket end date I don't need to put selected value around these because we have a row context here and in fact while I'm at it I might do a variable for this end time be the end time it's just good to put variables in here it just helps with the documentation it helps the reader understand how the variables are working so now I've got my start time and end time okay so now I need a new clause that handles this situation where the ticket is closed on the same day so I need to say if the start date is equal to the end date so if they open and close on the same day and double ampersand and if the end time so this end time is before the end of day which is the business hours end so here's the conditions if the ticket is open and closed on the same day and if the closed time is before the end of business day then we need to subtract the the end time from the start timer so I'm going to be able to reuse this piece of code here so I need to know the not the business hours end but the this end time subtract this start time and so on and I'll get rid of that comma otherwise it will be that time there so and I'll put a comment in here always good practice so there's that comments just so that I can understand what I did and why and which is very helpful for coming back in debugging okay back on track so I've solved that particular problem okay let's move on what happens if it's closes on a different day I need to know how much time has elapsed from the start of the closed day through to the end time on the closed day and I'm going to go through the same process as I did before so to work out the elapsed time on the last day I just need to do exactly as they did before now I'm going to try and write a related function so basically I want this same piece of logic here as I had before but remember that I need to use the other relationship I need to use the inactive relationship and not the active relationship so my intuition says go calculate related table and then use the other relationship in order to make this work but notice intellisense is saying that this is not correct so this is invalid syntax I'm actually a little bit surprised but you learn a lot just by writing formulas let's go ahead and write this out and put the other dates in there now I'm assuming that I have seen cases in the past where intellisense says something doesn't work but let's just go ahead and test this and let's see what happens but intellisense would be suggesting that this is not legal you're not allow able to use the related function using an inactive relationship so therefore I can't return this here I could try and do roulette calculator table so that also doesn't work okay so we need an alternative so I think the alternative that makes the most sense is lookup value so lookup value is also like vlookup but it doesn't rely on a physical relationship either active or inactive and so the best way to use lookup value is just read the intellisense it's back to front to vlookup so what result do I want to return well it's the working-day which column do I want to search I want to search the calendar date and what value do I want to search well it would be the end date so it's the opposite of lookup value so that should return either 0 or 1 and so I'll say if that equals zero then give me 0 and let's just check that and see whether that's working as we would expect so the question is are we ending on a working day or a non working day so non-working public holiday non-working non-working these are ending on a working day so that all seems to be working just fine so I'm going to still the rest of the business logic from here and I'm going to put a comma and let's go ahead and modify this formula now for the end time basically just doing the opposite us before okay so if the end time is less than or equal to the start of day then it's zero so if we close the ticket before the start of the day then we should return zero so there's no working time on that day and I've just gone ahead and edited the comment to make it clear if the end time is on or before the start of day then there's no time on the last day so let's move on to the next one so previously we're working out if the start time is on before then there's nine hours so in other words if the end time is greater than or equal to the end of day so if we close the ticket after the end of the day then its business hours per day so if the okay so is the updated comment okay let's move now this time we're saying if the start date is equal to the end date well we're capturing that scenario up here so we don't need to do this again so if the start date is equal to the end date then we just want zero and then the last one is otherwise and we just have to flip that so the business I'm still missing a comma up here so we take the business hours start and we naturally need the end time so this will be the end time tract the business our start times 24 okay so let's go have a look at how many hours on the last day and we'll just do a couple of samples so public holiday weekend weekend so there's zeros this one's on a Friday closing at four thirty three that's half an hour before the end of the day that looks about right Monday this one's closing before the start of day so zero on the end day that looks good Tuesday it's closing about 2 p.m. so that's about six hours in so that looks pretty good this one's closing on a working day after the end of day so nine hours so that all looks like it's working correctly okay so the next part of the problem is we need to know how many full working days are there between the start date and the end date not counting the start date and the end date so here's a formula that I worked out before I'm using the dates between function here I learnt from Phil Simic that dates between is more efficient than using the filter function so I always use dates between now so what I want to do is I want to add up the numbers in this working day column remember zero for a nonworking day one for a working day and I want to filter the calendar table to be between the start date and the end date so this will tell me how many working days there are now in actual fact this formula is not correct but let me show you what I'm talking about so let me go ahead and show you the result so this is now working days let's go and take this one here so Monday the 17th to Thursday the 20th so Monday Tuesday Wednesday Thursday that's correct it's for elapsed days but it includes the Monday and the Thursday and if you look at the logic that I built I don't want to include the start day or the end day I only want to include the dates in between so I need to adjust this I need to add one to the start date and I need to subtract one from the end date so let's keep that row there Monday the 17th let's have a look and here's Monday the 17th is now two additional working days and so I've just made a comment there that why I've adjusted the start date and they saw no for later on okay so now I've got all the information I need to work out the number of hours in between so the total hours is the hours on the first day plus the full working days times by nine hours per day plus the number of hours on the last day and finally I'm going to return the total hours so let's go ahead and have a look at this formula so there's the final formula let's have a look at a couple of examples Saturday the fourth of January it was opened on a nonworking day it was closed on a nonworking day there are five business days of nine hours long nine five 2:45 there's the business hours open if I take another example here February the 12th just before twelve o'clock so roughly so five and a quarter hours on the first day and then there was Thursday and Friday so that's another 18 plus call at 5:00 is 23 and then it was closed and so we've got another nine hours so thirty two point two so that number is correct so there you have it that's the solution I will post this final formula and the workbook on my blog you can take a look at it the key learnings that you need to take away are the process that I use to solve these problems and so if you like the way I teach Dax if you want to learn more from me there are various ways that you can learn probably the most accessible ways for most people around the world are to read my book there's a link there to both versions of my book or you can attend my supercharged power bi online semester base training which is now offered through schoolwave and there's a short code there for you to find out more details about that

Channel: Excelerator BI

Views: 10,204

Keywords:

Id: _O8o6Hxuyjk

Length: 41min 3sec (2463 seconds)

Published: Wed Jun 24 2020