How to create a GANTT CHART to track project and tasks in Power BI using the default Matrix Visual

in this video i'm going to show you how to create your own gantt chart using the default matrix visual in power bi we're going to go through it step by step together so you can follow along as well all of that and more so without further ado let's get started hi my name is fenan and welcome to the solutions abroad youtube channel where we cover tips tricks and best practices when working with power bi i upload new videos every week so make sure you hit that subscribe button and the bell icon to get notified when a new one is out so let's jump right into it here i have a empty report with two tables in my data sets look here we have the calendar table which is what we use for our time intelligence calculations and here we have a project table which just has some information about the projects itself so the type of tasks that you have for each of those uh which project do they belong to their start dates and finish dates as well as their status so if they're completed if they're late if they're in progress or not started so we have all the data that we need in order to create a gantt chart visual if you don't know yet gantt chart is just an easier way for you to visualize your projects and tasks within a certain time frame you can use custom visuals you can import them into your power bi desktop but in this video we're going to show you how you can use the default matrix visual and convert it into a gantt charts that you can use for your reports so the first thing that we'll do is we'll head on to the model itself so we have the calendar and the project tables not related at the moment we need to create a relationship with them before we can start working on them on measures so to do that we'll just drag the date to the start date here so we have a relationship between these two columns the next thing that we do is we head over to our report view and let's start visualizing the project information on a matrix so i will start by selecting the project the task start dates finish dates and status here we go we're going to convert this into a matrix and we're going to just make sure this is set up correctly uh you'll see it's sort of minimized so we need to collapse that or expand it to the very bottom once that's done let's just fix this a little bit so under column headers well not column headers so under row headers we need to disable the stepped layout so that we can have all of these columns side by side and also i want to hide the plus minus icons just to make it a little bit so these are the columns that we need for our matrix and for our column we need to show the dates so we're going to just put the date from the calendar at the moment which needs we need to fix this a little bit because the views are a bit too wide but these uh columns will be where the gantt chart will be so a couple things to fix so first we need to fix the view for the dates we need to reformat that into something that is a little bit smaller and to do that we're just going to click the date and under format on the ribbon up there we will just do format month slash date so that is the view format for these uh columns so here it shows you the various start dates uh for our project don't worry because when we add the measure it will fix itself but for now these are just the start dates corresponding to what we have in our projects table because if you remember we have the relationship between the dates and the start dates the next thing is the highlights because we want to dynamically highlight it using conditional formatting we want to just make sure that this has no formatting whatsoever so we'll just go to the format here under style change the default to none so you'll see that there's no more alternating highlights on each of those rows so the next thing is we need to do two things so first we need to create the measure to highlight the days within each task so for example we have this first task here the site apm process we want the range from the first on november to 8th of november to be highlighted similarly for this one as well first to the sixth vice versa vice versa so we need to create a measure so that we can put it on the values in our table or in our matrix to highlight those uh cells so let's do that i'm not going to create a calculations measure table we're just going to put it in the projects table to keep it simple we're going to name this one um column values so first let's create a variable so the first variable that we need to create is the start date so this is the start date for the calculation that we'll need to write for the period so we're gonna type calculate here and here we're going to say give me the lowest value in my project date and then we're going to use all to say ignore any filter context applied to the um to the projects table so what that will give us is the range of the columns that will be visible in our matrix so we'll do the same thing for the end date so we'll do the opposite so here we're going to do end dates and then instead of min we're going to do max so that will give us the opposite part and then this one we need to make sure we are pointing on the finish date so let me just do this finish date here we go so now that we've done that we'll now create the variable period which is what will determine the start date and the end date for each of the tasks so we're gonna write here minimum for the calendar date so in the range that we have there is it has to be greater than or equal to start date and we're going to do calendar date again i'm going to say if the highest calendar date is less than or equals to the end date so now that we have that we simply just return this value so now we have the value to say this if this is within a period or what do we do so we're going to add an if statement here to say if period is true then give me a one all right so we'll keep it like that for now and let's add this into our value as well uh so here we kind of have to fix a couple of things so we have a couple of subtotals that i don't want to see so we'll just disable those so here we go so you'll see that now we are using that period to mark the dates where uh the start date and finish dates are for each of those tasks so for example if you look at this one you see the 1st of november to the 8th of november and that is where the ones are and then where there aren't any ones it's blank because we decide that on the if statement so same thing with this other one so first to the sixth that's what is being highlighted or you have the one value on those columns the next thing that we want to do now is we want to determine what the color should be for each of those tasks now we're using numbers at the moment because we're going to convert them into colors later but for now we need to distinguish which tasks are late or completed not started or in progress depending on the status of those tasks and we want to highlight them accordingly so for example if it's complete we want to show them as green or if they're late we want to show them as red so something like this so to do that we need to distinguish the values all together so we have one two three four different statuses and we need to distinguish between those four different statuses so we'll do that in the measure itself so we're gonna go back to our measure here column values we're going to add a new variable called status and this variable is what we will use to determine the status for each of those tasks so we're going to say give me the maximum status which is just the unique status for each of those tasks and then we're going to do another all so give me without any filter context for the calendar and then on the return we're going to change this into a switch statement because we will now have more values to check so expression will need it put it as true and then here's where we will put our expressions so let's say if the period if you're within the period and if your status is completed give me one so this is what decides what the status should be so if the status is completed it has to be one and then we'll need to put different values for the different statuses so to make it easier we're just going to copy and paste we'll say if the status is late we want to make it 2 if the status is in progress make it three and then lastly if it is not started we'll make it four let's see if that works so here we go so now if i just deselect that for a second so you will see now that if it's late for example this task is late you will see that the number on our value is 2. if it's completed this one if it's not started it's four and if it is in progress it is three so very simple right so now that we've marked where the periods are for each of the tasks and what their status are now we move on to the cool stuff which is the conditional formatting right so to do that we're gonna go to the format pane over here we go to conditional formatting and we're gonna first just try to change the background color so we've hit advanced here we're going to define the color based on certain rules so we'll say we're going to create 4 and this corresponds to the numbers that we've put in there so if we want is is value so if it's completed late in progress and i don't remember what this one is for is not started so one is completed so it should be green so we'll just pick a random green color here it doesn't matter for two it should be late so it's red in progress we can mark it as blue and then not started we will just mark it as gray for now so if we hit okay oh so it didn't so actually that made a mistake there so if we hit advanced controls again i need to change this as numbers not percents if we hit okay once more so there you go so it's now marked those specific tasks within our gantt chart we just need to kind of hide the numbers and to do that we will just need to do the same thing but on the font colors just so that they blend in the same way so advanced controls once again we use rules we do four different rules here we just do is we do one two three four we'll do numbers and the colors green red blue and gray and there you go so you now have a gantt chart that marks out the specific tasks within dates and even shows you or color codes to you what the statuses are for each of those tasks so for this example we're just showing november so you see it doesn't go that far but if you have a lot of dates and you might want to use a sort of date slicer for that you can use something like this so you can get a date here put it in a slicer so you have the slider visual here and then you just need to make sure that the column values are set to is not blank and there you go so if you want to change the range of your gantt charts this will cross-filter so you'll see that it will show you from the 7th of november onwards and same way that you would use with the other visuals and that's really it for this video i hope i've helped you understand how easy it is to implement something like a gantt chart using the default visuals in power bi thanks for watching as usual give this video like if you found it useful give it a dislike if you didn't so i know to do better for next time ask your questions in the comment section box below so i can help you and you can help others if you really like this video we have a patreon page where you can support the channel and get exclusive perks like early access demo files and credits at any of these videos thanks again for watching and see you in the next one bye

Channel: Fernan

Views: 34,932

Keywords: solutions abroad, power bi, powerbi, power bi tutorials, power bi for beginners, beginners guide to power bi, data analytics, dax, data modelling, data visualisation, business intelligence, power bi 2021, how to power bi, power bi how to, power bi best practices, power bi tips and tricks, power bi standards, power bi patterns, power bi help, power bi tips

Id: 8pbFvBMT9L8

Length: 15min 0sec (900 seconds)

Published: Wed Nov 24 2021