Get more out of a MATRIX VISUAL in Power BI | Turning a matrix into a GANTT CHART 

Bas - I love the way you produce videos on topics that make me think out of the box about what can be done in Power BI. It may look on the surface - That the subject matter is very niche, but in reality, these obscure topics usually produce measures, that can be applicable to more commonly found problems. Thanks for this one, its really cool. (Loved the gem of a shortcut to turn off the auto-sizing of columns) ;)

I’ve pretty much told my team what they were asking for was impossible.. you may have just given me what I need. I’m going to need to highlight the start date and end date different colours so if I can figure out how to get that into the measure I’ll be flying. Thank you. Subbed and liked for appreciation.

Thanks a bunch Bas, this couldn't come at a better time, I've been looking for a clean simple solution to create a Gantt chart on PowerBI. Thanks again!

Many thanks for providing such a well explained video! I was looking for a way to do it in various sites and could not find it.

Amazing tutorial. I trully love your content!! I have a question, CAN YOU DO IT FOR HOURS ALSO AND DATES?!!

Nice approach. I love the trick for formatting the date column to be narrow. Thank you.

Another great video Bas!!! I really like your style. You present very well and clearly, making it much easier for anybody to pick this up. Well done, keep up the great work!

The Magic of DAX for Design is amazing.

Awesome and very clean video! Great example to show others how to build their Power BI and DAX skills and not just give the whole solution but help other learn how to build their DAX solution! Great job, thanks Bas!

I knew this had to be possible. You are awesome for sharing. Thanks, Bas!

Thank you Bas. I am inspired by your inspiration. You raise the bar with every video.

Dear Bas! Thanks a LOT for this tutorial! It is absolutely AMAZING! I was wondering, can we add here the dotted blue line for today's date, like you did in the bar chart Gantt?

Hii i love your teaching style and your tutorials are really helpful. Can you tell me how you edit your videos 🙏🏻🙏🏻🙏🏻🙏🏻 please reply

Awesome tutorial! I’m looking for a solution to a variation on the Gantt chart. To use your example, I’d like to show each category as a single line with the projects for each category visualized on that line. So, there would be multiple project bars per category line. I don’t have projects that overlap in each category but that repeat over time. Thanks for putting out such detailed content! Very informative and helpful.

Dude, you look like someone I could have a beer with. Thanks for this video, perfect timing for one of my projects.

That's awesome tutorial! wandering if we can fix column days as calendar days of each month, and show event bookings across years months with filter? thanks

Such a great video and breakdown! Thank you so much for posting this. I have several Power BI reports using Gantt charts and I've never liked the custom visuals. I found a resource when I started on the reports that used a matrix but, it was not as well explained and thorough as this video. Subscribed and getting notifications now!

Thanks for the great content, I’m just wondering if we can create a Gantt chart with group project stage in a single row ? Been looking elsewhere but couldn’t get any good solutions

I have a challenging question, maybe a new video idea? I would like to include in the Gantt chart also the lookout for the project. Project can have several phases: feasibility study, development, scale-up, launch. All those phases have start and end date. It would be wonderful to see them in one line for each project. eg. Mar-April Feasibility study, May-August Development etc... How would you show them in Power BI as a Gantt chart?

Awesome! I like the way you have explained about Gantt Chart in Power BI as I was looking for soming similar in Power BI as I have tried it in Excel with same method, Thank you very much!

Amazing tutorial. I trully love your content!! I have a question, What can we do if we don't have an end date yet for several of the tasks?

This is awesome!! thank you so much.... I do have a question, is there a way to add data labels to the bars, e.g. adding completion date to the bars

Looks promising. Could you share the fields in your custom date table and whatever relationships you've created on the back end? I've watched two of your videos now and neither shows or explains this table. Both seem completely dependent on the date table but provide no explanation. I'm not sure how everyone else commenting knew how to set this up. I continue to get the error "syntax for ')' is incorrect and although I've created the date, weeknum, etc fields it doesn't work. I notice the visible image / hierarchy for the data table doesn't match mine. I appreciate any help.

Hi Bas, like your help to see if any way to go next-level of this Matrix Gantt chart. For example, we have main project with different sub tasks scheduled by period. can we put all the sub tasks into one row and spread them in the same row by each task timeline, basically to replace the layout with horizontal way. Thanks for your help!!!

You're the best. No doubt about it. As always, thanks so much.

Hey, first of all, thank you so much for the video. Secondly, is there a possible way, to use conditional formatting, in a way, where every blank is filled with a different color? I have created a matrix, in which there are spaces between dates in all projects, and I want to fill between these dates with a color, that is different for every row.

Hi Bas, thanks for the video! When I set a project on 3th of January 2022, it returns week 2 instead of week 1, do you know what I am doing wrong? Thanks in advance!

Hi Bas, love your video and your explanation is loud and clear! I have a basic question, so in my Gantt chart, there's a process that will repeat every end of month. I've identified that in my data source, but when imported into the Matrix visual, somehow it only recorded only one week. Do you know why that is and if so, is there a way to make all the other weeks show up in my Matrix chart? Thanks so much!

The trick w/ the column header size is absolutely BRILLIANT, just saying.

Hi Bas! Thanks a lot for the helpful video. This is a nice workaround to avoid custom visuals. Is there any chance that you can go one step further and teach us how to create a Gantt Chart using SVG objects in a measure and the matrix visual? I saw a snapshot of a table that includes 4 columns for dates: baseline and current dates. Any advise will be much appreciated!

Thank you so much, Bas! Yet another spectacular video!

Hey Bas, great video! I have a separate table with offline weekend start & stop times. How can I add offline weekend days in a different color?

Hey Bas, great video! Would the same approach work when working with time instead of dates?

It is great as always! Many thanks! I am a citizen developer and really appreciated it!

Nice Video. Question : is there a way to merge the date header cells so that the month number appears only once in one large cell ?

Hello. Thank you for your videos. Really helpful. Can we have the sum of these 1s per column at the bottom of each column and how please?

My project dates between oct 2020 to 0ct 2021 and after adding conditional formatting dax, it shows only activities for 2020 , how to do.

That was excellent, can't wait to try it

Excellent tutorial!! Thank you!!👍🏽

Thank you so much for this video, I'm a beginner and this helped a ton with what I'm trying to do! I have some projects that have an end but not a start date, how would I write it so that these projects return an 0?

Thank you for the tips, I the project category formating, is taking the earliest finished project data when we close the expanding "+" any reason why? And like this give a wrong perspective when the table is minimized. Thanks for the help

💥💥 Always love your videos and always pick up something new - so THANK YOU 👍. One question theough..... why? Why would you do this over using the custom Gantt visual. I know you said you may not want to... but really can not think of a reason not to use the MS Gantt visual.

Hey Bas, love the video, very well explained. I've already built something very similar and came here hoping that you had solved how I could only show the Tooltip if I hover over a Matrix cell with a value >=1. Not sure if it's possible but if you have any ideas of where to start I'd be forever grateful :)

Hey Bas! Great video as always, love your stuff! 😊 I've been together with a colleague trying to create a Gantt Chart for vacation. The principle is of course exactly the same like the example you show. However, we have a weird bug/issue. When we add the date hierarchy, as we need to, since we want to see the year, month and day, we only get a "1" value for the first day of each period for the vacation. Kind of like you have the year, week and day. So for a project that starts for instance in September, and ends in mid October, we only see values for all the days in September, and nothing shows for October. I've been wondering how to get around that issue and if anyone has faced something simillar? 😃 Thanks

Hi, We have Projects with multiple Tasks. Users need a chart showing each Project duration based on Start and End date with Actual start and finish Dates. its Same for Task's also. How we can achieve this. Please help me here. Thanks in Advance, Hari

I was looking at your dimDate table and how to show that with Fiscal Year as month number 1? For example, April = Month 1

Unfortunately, this method only works when the min start date and max end date are within 100 days of each other, since the Matrix visual only allows for 100 columns. To get it to display periods outside that 100 day range, you have to filter the dates to a different 100 day range. This means that building a chart that covers an entire year (Jan 1 through Dec 31) isn't possible, unless you want to filter for blocks of 100 days...

Hello. Thanks a lot for making wonderful and super helpful videos. I have a question - Is it possible to make a Gantt chart in matrix visual for a single project with multiple timelines?

incredible and amazing Bas. Very well done!

Creating magic with Power BI 🤯

Amazing Bas, thank you!

Thanks for the video. It was really helpful. Please let me know whether this matrix model will work if start date and end date are date time values and I want the data at an hour level. Purpose is to know number of people are online at that hour.

Hi! Thanks for the video. small question: how to show the project Start and End date on the roadmap? because for now it shows only Start Date

What if the project lasted for just one days.... Such project is missing from the Gantt chart. Could you please please advise. I'm building a report to track which all users are out of office or on vacation. Based on current logic users who is out of an office for just one day, is missing from the gantt chart. @how to Power BI

Thanks for sharing BAS, this is a really good one but as unfortunately the Matrix visual is limited to 100 columns so with a day granularity, we can just show 100 days. By the way, the columns width trick is incredible

Hey Bas, another fantastic video! Although I was aware of some of the tricks here but you took it up a notch. Keep it up. I was wondering if you could do a video on incremental refresh when combining files from a sharepoint site?

any date between project start and end date can be considered as milestone/go live date & is there way we can highlight this date

Is there any way to make it show the results of a calculation instead of a "1"? I'd like for it to show how many man-hours we need to deploy/produce per week so I'd like to show that in each square. For example, if we need to use 1000 hours over 10 weeks (= 100 hours per week) then I know I'd to send more than 1 person to get the job done. When I try to replace the "1" then it only shows the calculation in the start date week.

For anyone wondering how to have more than one sequence of dates in the same line (ie, more than one start/end date per project): 1) in a new column called "DateRange", create a list of all the dates between start and end and expand it (there are videos on youtube teaching how to create a list from a range of dates) 2) create a measure that returns 1 when dimDate equals DateRange or 0 otherwise It would be something like this: CF Gantt = IF ( HASONEVALUE(dimDate[DateRange]), IF ( VALUES(dimDate[Date]) = MAX(Project[Date]), 1, 0 ), BLANK() ) the rest is the same

Did you cover how the "dimDate" table was created or the data structure within it?

Would it be possible to add subtasks?

Hi, i would like to show whole year date instead of the duration date. May i know how to change on the measurement?

Hi Bas, really like your video's! 1 remark, I noticed you use the system 2 argument for the weeknum function. This returns the wrong weeknumber for some years because it doesn't take the European ISO weeknumbering standard into consideration. It's better to use system 21 like we have in Excel. This is an undocumented feature which I recently discovered in the MS documentation updated in june of this year. If you use weeknum, 2 and weeknum, 21 in the same calendar you wil notice a difference in weeknumbers of 1. I try to teach this in my Excel and Power BI courses. Hope this helps.

Hey Bas! Thanks for another great video. I am trying to do something very similar.. but I am having issues. The problem I have is that I have a "TIME" table with 12 hour time table. I have a "CALLS" table with start and end times (durations) that I am trying to add into the same sort of matrix/gant. The problem is the durations between the start/end date .. and well as the end date.. dont show in the proper "bucket". I am kind of stuck on this one and have tried everything. Any suggestions on this? working with durations (HH:MM:SS) seems to be fairly difficult for me. I am basically trying to show, through the work day, if specific agents were on the phones or not throughout the whole shift.

Man this helped me today. Except, that the tooltip doesn't work after the current year. :(

Hi Bas, fantastic video, thanks a lot! Does this solution works with time slicer? When I add slicer from date table it does not filter the Gantt table and it shows still all dates as of first starting date, what I do wrong :(?

godfather of the power bi ,,

Thanks for this video!!! My gantt chart looks gorgeous. Looking for a way to grey out the weekends, anyone did the same?

I want to show timeline even on the minutes, Here you show them only in days. Do you have any suggestion for that

is there a possibilty to display all the data without being able to scroll horizontally and vertically.

this is awesome thank you

Very nice, thx Bas.

Hey Bas, can I ask for that hoodie you're wearing. I so love it. It would be great if you can send me one :)

First thing -THANK YOU for all your work. It is brilliant! Second thing - if you can spare 2 minutes to just let me know HOW you can insert values in the project row if it doesn't match the criteria. For example ....if the project starts on 1.01.2022 and ends on 01.05.2022 I will see 1 in the Matrix and I can achieve that...however, I WANNA also see something after 01.05.2022, let's say 100. However, every time when I use false statement to include that the matrix automatically provides ALL projects and not the projects that should be filtered in the first place :(

Can it work with hours and minutes in a day?

HI boss, Could you be able to do a video for using Time in Bar Chard, based on call centre data

Hi Bas, can you make a custom visual that include stacked bar chart and matrix in one?

How to add a label in right of each "bar" t oshow a completion %? and how to add a constant line with today?

Thanks for sharing this

We're going to use this method to create a matrix that shows when employees are off on holidays!

Existe essa explicação em português?

Brilliant!!!

it's useful to me

Great 👍🏼

HOW TO SHOW CURRENT DAY LINE INDICATOR IN MATRIX GANNT CHART

Seems complicated. Tableau has a food Gantt chart