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.
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.
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 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.
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.
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.
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.
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.
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.
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!
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"))
Should you use that, your schedule will seem like this:
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!
