Sunday, November 13, 2022
HomeData ScienceMaking a Gantt Chart with PowerBI | by Abdullah Kurkcu | Nov,...

Making a Gantt Chart with PowerBI | by Abdullah Kurkcu | Nov, 2022


After failing many instances to create a Gantt Chart utilizing present visuals, I made a decision to construct one myself utilizing a matrix visible in PowerBI

There was truly a PowerBI visible that I used to be already utilizing for creating Gantt charts. Nonetheless, once I tried to share it, I noticed it was unimaginable with out getting a paid subscription. You want a subscription not just for your self but in addition everybody else that should see your Gantt Chart.

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

I believed the simplest approach to construct it was to make use of a Matrix visible in PowerBI to create one much like the way you create them in Excel, by altering the cell colour to make the cells seem like schedule progress bars. Then, my wants immensely elevated as a result of I needed to plot a line for immediately’s date, I needed to differentiate between processes and milestones, and so forth.

I discovered many movies on-line, they usually have been immensely helpful. Right here is one among them.

The very first thing is to load knowledge to PowerBI. I used to be working with Primavera schedules. When the Primavera schedule is exported to an Excel file, it robotically divides the degrees of processes through the use of varied indentation ranges. Indentation doesn’t correctly work for PowerBI, so, should you can label every activity with some course of ID, it really works like a attraction. Here’s a snapshot of my schedule knowledge.

Right here, the Sub ID just about defines the extent of the method whether or not it’s a firm activity, milestone, or conceptual design. This manner the dashboard can construct the connection between varied duties, and might perceive and visualize processes individually.

The picture under exhibits the add display on PowerBI. As soon as it’s uploaded, use the primary row as headers if it’s not already executed so. Make it possible for your Begin and End dates are in the correct format. I additionally made the beginning and end dates the identical for milestones in order that it gained’t colour a cell when these two dates are the identical. As an alternative, it would present an icon for a milestone.

Right here is the code within the Superior Editor should you want it.

As you’ll be able to see, I outlined the column kind for every column within the DAX code.

As soon as the info is uploaded to your dashboard, create an empty matrix visible. Begin including firm identify, Sub ID, Exercise ID, Exercise Identify, Begin and Finish into the rows part of the visible.

Make certain to take away date hierarchies by clicking the dropdown and deciding on Begin and End.

When you add all these columns, you’ll understand that there’s now a “+” button subsequent to the corporate identify. Should you increase all ranges, it would seem like the picture under.

The very first thing that you must do to vary that is to show off the stepped structure possibility. Should you click on on the matrix visible after which format visible, that you must discover the “Row header” dropdown. There, you will note the “Choices” button on the backside.

It’s also possible to flip off row and column subtotals on this view

The identical choices web page allows you to flip off +/- icons as effectively. Now, your matrix visible ought to seem like the picture under.

We will use a filter on this web page to not present any row that has “NA”. On the filters pane, choose Sub ID after which choose all. Unclick NA and that can take away the rows which have NA because the sub-id out of your visible.

To create date brackets, we’ll want a chunk of DAX code. Return to your Mannequin display on the left menu and let’s generate a calendar for our schedule.

Click on “New Desk” within the high menu beneath “Calculations”. This may create a brand new empty desk in your mannequin.

As soon as your click on “New Desk”, PowerBI will allow you to write a DAX code beginning with “Desk =”. Right here is the code we’ll use to create our calendar. Our dates will begin from 01/01/2022 and go all the way in which to 2026. You don’t have so as to add quarters, weeks, and/or days should you don’t want them in your schedule.

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

In case your calendar is created, that you must add the date variables to your columns of the matrix visible. I’m solely going so as to add Yr & Quarter.

While you populate the columns with dates, your visible will fail, and you will note a message saying “Can’t show the visible” since there aren’t any values related to these dates that we created.

The subsequent factor is so as to add some values to those date columns we created. We have to write a operate that appears on the course of dates and provides 0 or 1 based mostly on the date vary. If the date vary (begin and finish of the challenge) coincides with the calendar dates we created, the cell ought to have 1, in any other case 0. To try this, that you must create a brand new measure in your schedule knowledge.

BetweenDates =     VAR starting = min ( Sheet1[Start] )     VAR end = max ( Sheet1[Finish] )     VAR period = max ( Sheet1[Original Duration] )     VAR colorVar = 0RETURNSWITCH (TRUE(),AND ( MAX ( 'Calendar'[Date] ) >= starting ,  MIN ( 'Calendar'[Date] ) <= end ) && period >0,1
)

We additionally wish to present a line for Right this moment’s date and use an icon for instance milestones. Subsequently, the code will first verify if the beginning and finish dates are the identical for a course of. If they’re, it would fill that cell with a⭐. In any other case, it would use “|” in a cell to mimic a line by repeating it in every row. We must always create one other measure known as “TodayLine” for this.

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

When you create these two measures, drag TodayLine to the Values of your matrix visible.

TodayLine operate will spotlight immediately’s date. It would additionally add a⭐ if the method begin and finish dates are the identical.

The very last thing we have to do is to vary cell colours within the matrix visible based mostly on the BetweenDates measure that we created.

To try this, click on in your matrix visible, go to its Visible settings and activate the background colour. After that, click on on the fx icon to arrange the correct coloring scheme.

When you click on on fx, the enter display would require a format type. Choose guidelines after which discover your BetweenDates area to base the rule. If the cell is the same as 1, it ought to have a colour.

Now, our schedule is prepared, and it ought to seem like the picture under!

If you choose “Columns” utilizing Drill on (highlighted) after which click on “Increase all down one stage within the hierarchy”, the schedule will present extra date options such because the quarter.

There’s a approach to colour every of those processes by course of kind. We have to create one other measure to distinguish varied duties by colour. Should you don’t want it, you’ll be able to skip the following half.

If it’s not a milestone and we wish to colour it by its course of identify, the DAX code ought to be structured as follows:

Shade by Course of =     IF ( [BetweenDates] <> 0 ,          SWITCH (              MAX ( Sheet1[Sub ID] ),              "Conceptual Design", "#14375A","Firm Duties", "#CC7B29"))
This may give a unique colour to numerous processes. Nonetheless, it would take away the coloring for processes like Milestones. I didn’t want to paint common processes. That’s why I used this operate.

Should you use that, your schedule will seem like this:

Every course of makes use of an HTML colour code. If you want to discover a colour, you should utilize an internet useful resource like https://htmlcolorcodes.com/

In conclusion, everyone knows that Gantt charts are a well known and profitable means of challenge administration and scheduling. An efficient, environment friendly, and sensible methodology for planning, monitoring, and controlling processes in initiatives the place quite a few actions are meant to be achieved inside a selected time vary is to create dashboards with embedded Gantt charts.

I hope that making a dashboard with a Gantt chart will make sure that every little thing is completed on schedule, allow you to simply visualize essential dates, and milestones, and set dependencies in your initiatives.

When you’ve got any questions, go away a remark.

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

Thanks for studying!

RELATED ARTICLES

LEAVE A REPLY

Please enter your comment!
Please enter your name here

- Advertisment -
Google search engine

Most Popular

Recent Comments