October 3, 2024

[ad_1]

After failing many times to create a Gantt Chart using existing visuals, I decided to build one myself using a matrix visual in PowerBI

There was actually a PowerBI visual that I was already using for creating Gantt charts. However, when I tried to share it, I realized it was impossible without getting a paid subscription. You need a subscription not only for yourself but also everyone else that needs to see your Gantt Chart.

Photo by Bich Tran: https://www.pexels.com/photo/photo-of-planner-and-writing-materials-760710/

I thought the easiest way to build it was to use a Matrix visual in PowerBI to create one similar to how you create them in Excel, by changing the cell color to make the cells look like schedule progress bars. Then, my needs immensely increased because I wanted to plot a line for today’s date, I had to differentiate between processes and milestones, and so on.

I found many videos online, and they were immensely useful. Here is one of them.

The first thing is to load data to PowerBI. I was working with Primavera schedules. When the Primavera schedule is exported to an Excel file, it automatically divides the levels of processes by using various indentation levels. Indentation does not properly work for PowerBI, so, if you can label each task with some process ID, it works like a charm. Here is a snapshot of my schedule data.

Here, the Sub ID pretty much defines the level of the process whether it is a company task, milestone, or conceptual design. This way the dashboard can build the relationship between various tasks, and can understand and visualize processes separately.

The image below shows the upload screen on PowerBI. Once it is uploaded, use the first row as headers if it is not already done so. Make sure that your Start and Finish dates are in the right format. I also made the start and finish dates the same for milestones so that it won’t color a cell when these two dates are the same. Instead, it will show an icon for a milestone.

Here is the code in the Advanced Editor if you need it.

As you can see, I defined the column type for each column in the DAX code.

Once the data is uploaded to your dashboard, create an empty matrix visual. Start adding company name, Sub ID, Activity ID, Activity Name, Start and End into the rows section of the visual.

Make sure to remove date hierarchies by clicking the dropdown and selecting Start and Finish.

Once you add all these columns, you will realize that there is now a “+” button next to the company name. If you expand all levels, it will look like the image below.

The first thing you need to do to change this is to turn off the stepped layout option. If you click on the matrix visual and then format visual, you need to find the “Row header” dropdown. There, you will see the “Options” button at the bottom.

You can also turn off row and column subtotals in this view

The same options page lets you turn off +/- icons as well. Now, your matrix visual should look like the image below.

We can use a filter on this page to not show any row that has “NA”. On the filters pane, select Sub ID and then select all. Unclick NA and that will remove the rows that have NA as the sub-id from your visual.

To create date brackets, we will need a piece of DAX code. Go back to your Model screen on the left menu and let’s generate a calendar for our schedule.

Click “New Table” in the top menu under “Calculations”. This will create a new empty table in your model.

Once your click “New Table”, PowerBI will let you write a DAX code starting with “Table =”. Here is the code we will use to create our calendar. Our dates will start from 01/01/2022 and go all the way to 2026. You don’t have to add quarters, weeks, and/or days if you don’t need them in your schedule.

Calendar =VAR     _start = DATE(2022,1,1)VAR     _end = DATE(2026,1,1)RETURN     ADDCOLUMNS(          CALENDAR(_start,_end)          ,"Year", YEAR([Date])          ,"Year Month", YEAR([Date]) & " " & FORMAT([Date], "mmmm")          ,"Quarter", "Q" & QUARTER([Date])          ,"Month Number", MONTH([Date])          ,"Month", FORMAT([Date], "mmmm")          ,"Week", WEEKNUM([Date], 1)          ,"Day", DAY([Date])          ,"Date Slicer"               , IF([Date] = TODAY(), "Today"                   , IF( [Date] = TODAY() - 1, "Yesterday"                   , FORMAT([Date], "dd mmmm yyyy") & ""                 )          )
)

If your calendar is created, you need to add the date variables to your columns of the matrix visual. I am only going to add Year & Quarter.

When you populate the columns with dates, your visual will fail, and you will see a message saying “Can’t display the visual” since there are no values associated with these dates that we created.

The next thing is to add some values to these date columns we created. We need to write a function that looks at the process dates and adds 0 or 1 based on the date range. If the date range (start and end of the project) coincides with the calendar dates we created, the cell should have 1, otherwise 0. To do that, you need to create a new measure in your schedule data.

BetweenDates =     VAR beginning = min ( Sheet1[Start] )     VAR finish = max ( Sheet1[Finish] )     VAR duration = max ( Sheet1[Original Duration] )     VAR colorVar = 0RETURNSWITCH (TRUE(),AND ( MAX ( 'Calendar'[Date] ) >= beginning ,  MIN ( 'Calendar'[Date] ) 0,1
)

We also would like to show a line for Today’s date and use an icon to illustrate milestones. Therefore, the code will first check if the start and end dates are the same for a process. If they are, it will fill that cell with a⭐. Otherwise, it will use “|” in a cell to imitate a line by repeating it in each row. We should create another measure called “TodayLine” for this.

TodayLine =    VAR TodayLine = "|"    VAR beginning = min ( Sheet1[Start] )    VAR finish = max ( Sheet1[Finish] )    VAR duration = max ( Sheet1[Original Duration] )RETURNSWITCH (     TRUE (),     duration == 0 && MAX ( 'Calendar'[Date] )  >= beginning && MIN       ( 'Calendar'[Date] ) <= finish ,  "⭐",     ISINSCOPE( 'Sheet1'[Sub ID]),          IF ( TODAY () IN DATESBETWEEN ( 'Calendar'[Date], MIN ( 'Calendar'[Date] ), MAX ( 'Calendar'[Date] ) ), TodayLine, ""))

Once you create these two measures, drag TodayLine to the Values of your matrix visual.

TodayLine function will highlight today’s date. It will also add a⭐ if the process start and end dates are the same.

The last thing we need to do is to change cell colors in the matrix visual based on the BetweenDates measure that we created.

To do that, click on your matrix visual, go to its Visual settings and turn on the background color. After that, click on the fx icon to set up the right coloring scheme.

Once you click on fx, the input screen will require a format style. Select rules and then find your BetweenDates field to base the rule. If the cell is equal to 1, it should have a color.

Now, our schedule is ready, and it should look like the image below!

If you select “Columns” using Drill on (highlighted) and then click “Expand all down one level in the hierarchy”, the schedule will show more date features such as the quarter.

There is a way to color each of these processes by process type. We need to create another measure to differentiate various tasks by color. If you don’t need it, you can skip the next part.

If it is not a milestone and we want to color it by its process name, the DAX code should be structured as follows:

Color by Process =     IF ( [BetweenDates]  0 ,          SWITCH (              MAX ( Sheet1[Sub ID] ),              "Conceptual Design", "#14375A","Company Tasks", "#CC7B29"))
This will give a different color to various processes. However, it will remove the coloring for processes like Milestones. I didn’t need to color general processes. That’s why I used this function.

If you use that, your schedule will look like this:

Each process uses an HTML color code. If you would like to find a color, you can use an online resource like https://htmlcolorcodes.com/

In conclusion, we all know that Gantt charts are a well-known and successful way of project management and scheduling. An effective, efficient, and practical method for planning, monitoring, and controlling processes in projects where numerous activities are intended to be accomplished within a specific time range is to create dashboards with embedded Gantt charts.

I hope that creating a dashboard with a Gantt chart will ensure that everything is finished on schedule, let you easily visualize crucial dates, and milestones, and set dependencies for your projects.

If you have any questions, leave a comment.

Dashboard Github repo: https://github.com/ak4728/PowerBI-schedule

Thanks for reading!

Creating a Gantt Chart with PowerBI Republished from Source https://towardsdatascience.com/creating-a-gantt-chart-with-powerbi-a6039dad7fe4?source=rss—-7f60cf5620c9—4 via https://towardsdatascience.com/feed

<!–

–>

[ad_2]

Source link