PROJECT MANAGEMENT in Power BI? Create a GANTT CHART from a bar chart | NO CUSTOM VISUAL solution
Ғылым және технология
GANTT Charts are very useful for project management. In this video I walk you through how you can turn a normal native bar chart into a GANTT Chart, without having to rely on custom visuals.
Enjoy the video and subscribe for more Power BI hacks!
Download file here datatraining.io/powerbi-how-to
--------------------------------
📊 TRAININGS 📊
---------------------------------
Power BI Design 4 Weeks Transformation Program my.datatraining.io/pages/powe...
Power BI Essentials datatraining.io/powerbilearni...
Business User Training datatraining.io/powerbi-busin...
For Custom Trainings and Consulting email directly support@datatraining.io
---------------------------------
⏱️ TIMESTAMPS ⏱️
---------------------------------
0:00 Intro
0:42 Step 1: Creating a bar chart as a base for the GANTT
1:55 Step 2: Creating the measures to push the bars to the right
5:30 Step 3: Making the GANTT pretty
7:35 Step 4: Adding date indicator lines
12:53 Step 5: Apply conditional formatting based on project status
15:34 Step 6: Fix the tooltip
16:11 End
---------------------------------
😍 JOIN 😍
----------------------------------
Join bit.ly/4b453bi
Subscribe bit.ly/31MnQGO
Insta / howtopowerbi
LinkedIn / basdohmen
TikTok / how.to.power.bi
X / howtopowerbi
fb / howtopowerbi
Threads www.threads.net/@howtopowerbi
Newsletter datatraining.io/newsletter
---------------------------------
👇 CHECK THIS OUT! 👇
---------------------------------
💻 My gear amzn.to/47F21Yc
📚 Power BI books MUST READ! amzn.to/3tUfFcj
💡 General books I recommend amzn.to/48YNo33
🎶 Music for my videos www.epidemicsound.com/referra...
🚀 For growing on KZread: www.tubebuddy.com/bas
🏄 Stuff I use daily amzn.to/3HqfMQ2
* Above are affiliate links, which means at no additional cost to you, if you make a purchase using these links we will receive a small commission. It supports us and helps us to continue making more How to Power BI videos!
Thanks for being a part of this channel and all your support! 💪 🙏
#HowToPowerBI #PowerBI #DataTraining
#powerbidesktop #powerbitraining #powerbideveloper #DAX
Пікірлер: 184
Very creative! Always amazed how much you are able to get out of the standard visuals
This is incredible how you managed it with only one measure. I wanted to do it with creating new table and some kind of coordinates for matrix in it. You saved me a time. Thanks
Everytime I see your video's I am left totally awestruck.. Hatts off to you
Great video, thank you for upload! I was requested to created Gantt chart on Sep 30... and on the same day you uploaded your video, I was sooooo lucky :) I could not figure out how to show task durations as relative and the solution was so simple! Thanks!
Awesome as always Bas, ty for this piece of art, hehe.
Really great! Learned many new things! I love how you could've stopped at pushing the bars to the right but you took the time to rake this chart to another level, making us learn new things
This is brilliant! Thank you!
wow! this is such a great video! i've recently started to learn Power BI. Your channel helps so much! I just subscribed to your channel! Thank you, Bas! : )
Excellent BAS, very good job, congratulations and to continue innovating in Power BI-thanks
Awesome Bas! Just stumbled on your channel today and I am hooked! your content is great, so useful and the way you walk through it is so engaging. Thank you for sharing your knowledge!
@HowtoPowerBI
2 жыл бұрын
Happy to hear this Tani!
Amazing video! Thank you for sharing these tricks. 👍
The best PowerBI content by far. So practical and looks good.
@HowtoPowerBI
2 жыл бұрын
thank you so much!!!!
@DanielTavares29
2 жыл бұрын
Totally agree
@perfectionist6624
Жыл бұрын
Yeah absolutely ✌
I found you on udemy and when I found your youtube channel I was Happy so I can learn more from you, Thank you sir (y)
First time seeing your videos, in less than a minute and already subscribed. It is right in the beggining of the video this is what every youtuber should aim for. THIS IS QUALITY CONTENT!!! Thanks a lot for your content!
@Locomaid
Ай бұрын
same!
thank you man, you are awesome.
Fantastic!!!! It is my favorite KZread channel!
@HowtoPowerBI
5 ай бұрын
Yey 🎉🎈☺️😄
Always Awesome content..Thanks for sharing
Thank you for what you do! 🙏
@HowtoPowerBI
2 жыл бұрын
thanks for watching!😊
Amaziiiiiiiiiiiiiiiiiiing my bro!
Great video. It helps a lot w/o using custom visuals. Quick question, why you have put Sep and Oct in Constant line section. Shall we dynamically show the months? Also is there any way to show the date timeline in x axis?
Thank you so much, Bas. That helped me a lot.
@HowtoPowerBI
5 ай бұрын
Awesome glad to hear it helped 😎😃
muchas gracias, excelente aporte
Great video, thanks!!! Maybe you can include the critical path in a next video.
This is great! Thanks a lot!!!
@HowtoPowerBI
2 жыл бұрын
Glad it was helpful, Dirk!
This is awesome. I could not find a visual that works in the marketplace and thought I was going to have to custom built one in python and I am not sure it would be even better then this!!! well doe and deff watching your videos from now on!!!!!! Plus your communication style is great. 😀
@HowtoPowerBI
14 күн бұрын
that's amazin 😁 thanks!
It seems several people are having the same error, The syntax for ')' is incorrect. Do you have any ideas? Code is exactly as in your video. Thanks!
@stavanparikh1668
Жыл бұрын
To resolve this error- after defining VAR Result, you need to return Result. i.e. last line of dax would be return Result.
@exhaustking9131
Жыл бұрын
Facing same error sir can you please elaborate...?
@dubsb540
3 ай бұрын
@@exhaustking9131 - I had the same problem, but writing "RETURN Result" on the last line fixes the problem!
Wonderful BAS you saved me a lot of time because I was just looking how to take an old Excel trick and apply it to Power BI
@HowtoPowerBI
2 жыл бұрын
Glad it helped Mehdi 😀! In the next video I create a gantt from a matrix - that one is very close to the excel gantt (with conditional formatting to determine the cell colors)
@mehdihammadi6145
2 жыл бұрын
@@HowtoPowerBI I have already explored this and the big drawback of the Matrix visual is that it is limited to 100 columns so grain size is not very nice or you have to use slicers. So I can't wait to hear about your approach.
Wow amazing, well done Bas!
@HowtoPowerBI
2 жыл бұрын
Thanks a lot Giavo! :)
Got the Nolan vibes in this video. I was already impressed with first 2 tricks, but you took us deeper with 3 more tricks. #inception
Good work I completed this Gantt chart thanks to your video
@eel3720
10 ай бұрын
I didn't understand why the data labels for the CL - Month1 was named September if Var Month1 = EOMONTH(OverallStartDate,0)+1
Fancy Bas, nice - thanks!
You are amazing .....
Great video ! Thx
@HowtoPowerBI
Жыл бұрын
:) thanks
Love you videos. I am super newbie. Had to use your video about dimdates. Not sure how you indent either. I also have same challenge as mentioned below for the PaddingLeft error.
@tania4277
Жыл бұрын
I take the bellow error in the DAX. You solve the error? The syntax for ')' is incorrect. (DAX(VAR OverallStartDate = [OverallStartDate]VAR StartDate =CALCULATE( MIN(Timeline[StartDate]), REMOVEFILTERS(dimDate))VAR Result = INT (StartDate - OverallStartDate)Return)).
Hello Bas, How can I show a constant line for the Start of the Year as oppose to EOM?
Thanks!
@HowtoPowerBI
2 жыл бұрын
Thank you Yvette for the support!
Amazing
great info. what if one of the projects' start date is empty? i tried your formula but gives me bad overallstartdate. thanks
super good!
@HowtoPowerBI
2 жыл бұрын
thx Matthias!!! 😀
Great video. Thanks for sharing.
@HowtoPowerBI
2 жыл бұрын
You are welcome!
Great video Bas! Thank you very much for the precious suggestions. I have only a little question for you. It could be possible to assign a parametric month name to the lines you have built using measures "Month1" and "Month2"? In the sample you have assigned directly September and October. Thank you. Antonio
I need a report like this but to show where the end dates have moved. I was thinking of extending the visual and having the new date being a different shade but there doesnt seem to be a way of doing this???
What if we want to show the dates on the x axis instead of Duration. and the same on the zoom slider.
About 13:50 you said something about using other functions instead of 'MAX' if we had more than one status. I'm working with a sheet that has more than one status, how could i do it? Thank you
I loved your content I have one question If we have dataset with project and individual task under project how can we go further. Please help on this one
Help I'm trying to follow along, but I'm having trouble with the conditional formatting the colors of the bars. I made the measures, but I can't add them to the values section. Any idea why? Also, how can we create a hierarchy label for the projects? I would like to have all the projects split up by the owner of the project. I can do that just fine for a vertical stacked bar chart, but not a horizontal stacked bar chart. Is it even possible to do that?
Good work thanks
@HowtoPowerBI
2 жыл бұрын
thanks a lot alassane! 😊
Amazing 😍
@HowtoPowerBI
15 күн бұрын
THANKS! 😁
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 matrix that includes 4 columns for dates: baseline and current dates. Any advise will be much appreciated! 😀
Thanks
@HowtoPowerBI
2 жыл бұрын
Hi Rajneesh, thanks a lot for the support!
Wonderful! Would never have thought this possible. Any ideas on how to get milestones into the chart? Would I have to overlay charts? 🤔
Hi, Bas! Great video as always 🔥 I'm working on moving dashboard from Qlik sense to Power bi and was a little upsetted, coz there is no duration format for visualizing time. So i just put value in time format in tooltips. Is any way to solve it?
AMAZING Job Thanks for sharing I really need it! I have couple of questions if anyone knows , why do we need the dates table for ? Also, I am having issue with month1 and 2 they are all set at the beginning, how may I troubleshoot this ?:)
wonderful piece of work. Thank you. Actually I was following this video but bumped with a problem. When I add the padding measure, the Legend stop working. Basically I can't have the breakdown of each bar. Any solution you can propose? thanks gain mate
Bus, can you please add if project has more than 3 type of duration on the same bar.... ? Thanks
the video is great, however, i don't see the values field so i can't put in the duration :( any thoughts on why I don't see it?
Great approach. I did one using a Matrix and formatting. Would this method allow you to add tasks per project? My core data I have task start and end dates, and multiple tasks per project. With a Matrix I could stagger the list levels for this. It does not look as clean as your method though
@HowtoPowerBI
2 жыл бұрын
not really - of course you could have project at level 1 and then tasks at level 2 (and then expand down and up) . However you dont have the plus icons as for the matrix and it always concatenates the labels which is not so pretty
Hey bro... nice tutorial! But i'm having an issue. i'm writing exactly what you writed on PaddingLeft new mesure, but it keep returning the following: the syntax for ')' is incorrect. Could you help me?
@tania4277
Жыл бұрын
I have the same error :( You solved?
Fantastic material Bas! One thing which is missing in my opinion - the mile stones on the charts. This would bring the full blown Gantt chart :-)
@HowtoPowerBI
2 жыл бұрын
good point!!! tricky one though 😁
@marcindabrowski1742
2 жыл бұрын
@@HowtoPowerBI You are our Power BI Master! I am sure you will handle it! :-)
@basdohmen
2 жыл бұрын
@@marcindabrowski1742 🤣 no pressure haha
@erikvanhurck
2 жыл бұрын
If I may promote custom visuals and my own channel here (Bas please let me know if you do not approve and I'll remove this comment). There are a number of visuals that have milestone options: kzread.info/head/PLBE7Yqq2lkv6-mN1EK2_ry7bai_zpTQ_2
@MuradYoloten
Жыл бұрын
@@HowtoPowerBI any luck ?
hello Bas , thank you for educating us. Small help ...if you can share both the data tables used to create the chart.
I love watching your videos. They are super helpful! I want to know if, by any chance, you have a solution where there are multiple date ranges for one project? can this be done with a bar chart or matrix chart?
@HowtoPowerBI
4 ай бұрын
for sure, you would need to adjust the measure to show a 1 for the relevant data ranges
Hi, as usual another nice tutorial. I want you please share a example of cutomer Questionaire of atleast 10 questions in some graphs. Answers of every question will be within (Strongly Agree, Agree, Neutral, Disagree, Strongly Disagree). can you do that?
very nice
@HowtoPowerBI
2 жыл бұрын
thank you Nagaraj!
Hellooo, How we put gradient color in single bar charts????
Great video, as always! Do you have any recommendations on how to add MM/YY to the X-axis instead of Duration in Days?
@HowtoPowerBI
Жыл бұрын
maybe better to go for this option if you need mm/yy kzread.info/dash/bejne/g6mbzaSqk5DQedY.html
Valeu!
@HowtoPowerBI
2 жыл бұрын
Thank you so much for the support Lean!
Hi, in a new version instead of values, one needs to define the y-axis and it does not allow adding multiple parameters. Any suggestions on this? Thank You
@HowtoPowerBI
4 ай бұрын
still works ... download the file (see description) and open it in the latest version
Hi Bas, it's very nice explanation. Thank you for that. I have a requirement on Project related information. I have columns Project ID, Status, Date in model. I have statuses like start, queue, execution, completed. In Date column, I have project start date and project status changed dates. When project 1's status changed from start to queue status, i need days from Previous status to current status like wise I need this for all the project statuses. I have used EARLIER function but I can't get the date properly. Can you please tell me the DAX code for this? It will be more helpful to me. Thanks in advance
Where's the overall start date measure coming from? I seem to have missed that :)
I've tried the min function multiple times but it keeps getting an error saying a single value can't be determined, I'm getting tired, someone can help me with it?
New to PowerBi - I need to understand what do you have in the Dim Date table and what columns have you linked on Project Table and Dim Date
If the same project has multiple start/end dates and runs at various times in the year.... I'd like to show each project running but I also want to group each project (by department) without using a legend.
What It Project A has more than one occurrence?
Amazing stuff. 👏 How about using dates instead of duration. Think that'll work?
@HowtoPowerBI
2 жыл бұрын
Thx Bolaji! 😀I don't think that will work when you use the bar chart as a visual (you need to have projects activities on the left axis and date on values wouldnt give you the size of the bars .. that's where you get stuck). However, it is possible when you take a matrix visual as a starting point (that's gonna be the next long video 😉)
@BolajiBI
2 жыл бұрын
@@HowtoPowerBI True True. Looking forward to that video.
i am trying to recreate this, but the fx button that shows up in minute 8:59 as you're developing the constant line does not show up. how can i get that to appear?
@HowtoPowerBI
2 жыл бұрын
the fx button only shows in pbi version aug 21 or later
Is anyone having Issues with the PaddingLeft variable? I am getting the following error "The syntax for ')' is incorrect. (DAX(VAR OverallStartDate = [OverallStartDate]VAR StartDate = CALCULATE( MIN('PBI Data'[Start Date]), REMOVEFILTERS(DatesTbl))))."
@chrisstapleton7415
Жыл бұрын
I went ahead and just used the gannt chart Microsoft came up with. You can search for it in the getvisuals section
@stavanparikh1668
Жыл бұрын
after defining Result you need to return Result. hence last line of Dax would be return Result
@d0zxe
3 ай бұрын
@@stavanparikh1668 Thank you.
Looks great, but I've fallen down on the very first step. My version of Power BI does not have a Values property for a stacked bar chart. I guess I will try X axis. Also had to find a video on how to create a date table and call it dimDate. I got to the 5.30 time and I guess x Axis is not the same as values, because my chart did not look anything like the video. So I gave up.
Cannot get the PaddingLeft DAX to work, BI keeps throwing a "The syntax for ')' is incorrect" error, but the Dax is exactly as per 5:27 in clip. Any ideas?
@HowtoPowerBI
2 жыл бұрын
can you copy paste your measure here ... check also if you have RETURN result in the end ..
@rodrigohuila
2 жыл бұрын
RETURN(Result)
I keep getting errors ion the PaddingLeft phase even thought it appears I'm following the DAX code to a T. I wish I could post a picture but does anyone have any advice?
@HowtoPowerBI
2 жыл бұрын
Could you post the formula and error?
@bailjr
2 жыл бұрын
@@HowtoPowerBI PaddingLeft = VAR OverallStartDate = [OverallStartDate] VAR StartDate = CALCULATE( MIN(****[Start Date]), REMOVEFILTERS(DimDate)) VAR Result = INT(StartDate - OverallStartDate) Return The error is that my syntax is incorrect.
@HowtoPowerBI
2 жыл бұрын
@@bailjr you forgot to write result after return and i m not sure what the **** are here?
@bailjr
2 жыл бұрын
@@HowtoPowerBI It worked when I added RETURN. The *** is the name of my spreadsheet...I wanted to protect the name of the document. Thanks!
@bailjr
2 жыл бұрын
@@HowtoPowerBI Quick question. When I do my PaddingLeft, I keep getting a really large number and in my case it is "-43456." I'm thinking that I need to convert the number to a date but I'm not sure where to do it...? Any advice?
Hello - great video! However, I received syntax errors as I followed along and PBI would not recognize the variables and measures created. Any tips to troubleshoot?
@HowtoPowerBI
Жыл бұрын
look for the first red squiggly line, when you hover over it tells you what the problem is. For example, if it tells you name not found, you've to check how you called the measure in your model.
@lindakeefe9591
Жыл бұрын
@@HowtoPowerBI Unfortunately, no squiggly lines. I just get an error "The syntax for ')' is incorrect.
@lindakeefe9591
Жыл бұрын
I figure it out. I needed to end the syntax with RETURN Result
@ejp5g
Жыл бұрын
@@lindakeefe9591 I think it's because he has a dimTable (that I didn't have while I was trying to follow-this)
I am trying your "PaddingLeft" DAX but I am getting this error: The syntax for ')' is incorrect. Followed your instructions. What is the missing link? Thank you
@HowtoPowerBI
2 ай бұрын
There is a typo somewhere in your code
@williamcochran164
Ай бұрын
It should be: RETURN Result. Video cut off the last word.
What is the dimDate Measure and how was it calculated?
@rubensanchezgil722
2 жыл бұрын
It is not a measure, it's the name of the Date table (which is, of course, a dimension). You can see it at 12:13.
@HowtoPowerBI
2 жыл бұрын
thx for answering Rubén
Awesome walkthrough but how can I add a holiday in the Gantt chart?
@954giggles
2 жыл бұрын
You can probably add a Holiday table a your project dataset
I keep getting an error when trying to create the Padding Left measure..... The syntax for 'VAR' is incorrect. (DAX(VAR OverallStartDate = [OverallStartDate]VAR StartDate = CALCULATE( MIN('OT 53s'[Account Create Date]), REMOVEFILTERS(dimDATE )VAR Result = INT(StartDate - [OverallStartDate])RETURN)).
@HowtoPowerBI
11 ай бұрын
You are missing at the end: return result
@erinbeverage5236
11 ай бұрын
@@HowtoPowerBI THANK YOU!
Your padding left measure kept throwing me a ")" error. Followed your other video on creating the date table. Was never able to figure out why. If anyone has had a similar problem pls comment, would like to fix as this is the only solution I've seen on the internet outside of custom visuals. Thx!
@HowtoPowerBI
2 жыл бұрын
thanks for watching Shanee! Would like to help, but not sure what you mean with a ")" error. Could you give more details?
@shaneemadden135
2 жыл бұрын
@@HowtoPowerBI I keep getting this error when I try your “PaddingLeft” measure: The syntax for ')' is incorrect. (DAX(VAR OverallStartDate=[OveralStartDate]VAR StartDate= CALCULATE( MIN(Projects[Start Date]), REMOVEFILTERS(dimDate) )VAR Result= INT(StartDate-OverallStartDate)RETURN)). I have tried in two workbooks following your example on the video and using the dimDate table example in another one of your videos. I cannot figure out why my version of PBI does not like this syntax. I am new to this so no frame of reference. Thanks for the reply.
@HowtoPowerBI
2 жыл бұрын
@@shaneemadden135 after the return statement you have "))." why? and why do you have (DAX( at the beginning ?
@shaneemadden135
2 жыл бұрын
This is only from the error message. It turns into that. My actual measure looks exactly like yours in the fx bar. The overallstartdate measure worked perfectly but the removefilters for the date table crapped out. All the syntax looks right as it does on your screen. Same colors, same indent. I probably have an issue with the underlying data. Thanks for your response though. Much appreciated!
@Deimos3D
2 жыл бұрын
@@shaneemadden135 did you figure out a solution. I am having the exact same problem
Hi, can you add multiply milestones in this gantt chart ?
@HowtoPowerBI
2 жыл бұрын
bit tricky with this approach ( or you do it like the month lines) - maybe better to build it using a matrix visual (see video here: kzread.info/dash/bejne/g6mbzaSqk5DQedY.html )
@ubonguttah
2 жыл бұрын
@@HowtoPowerBI Thanks for your reply, but we wanted a situation we can see each miletones , and there are many milestone .. thanks
Really interesting and there is a ton of back episodes I'd love to look at too. Question: 4m.15s and PaddingLeft = : VAR OverallStartDate = [OverallStartDate] Do we need this VAR, can't we just use directly the measure you already created? And if not - what is the rationale for using it? You already have named it very clearly, so it is clear for anyone to understand.
@krishanmw1
2 жыл бұрын
I'm getting a syntax error
@bailjr
2 жыл бұрын
@@krishanmw1 Same here!
@tobyheikkila6818
Жыл бұрын
The syntax for ')' is incorrect.
@justmejustme4444
Жыл бұрын
@@tobyheikkila6818 I have the syntax error too - did you manage to fix?
@meabhnimhuilleoir4762
Жыл бұрын
@@justmejustme4444 I'm getting this error too - did you have any luck fixing it?
what is dimdate?
@HowtoPowerBI
7 ай бұрын
kzread.infoMChjMrjgn1Q
How can we create a stacked can’t chart?
@HowtoPowerBI
2 жыл бұрын
Lol 😀
@isameneses12
2 жыл бұрын
I have the same question. It may sound weird but that visual could show tasks per project without drilling down. Did you find a way to do it?
Can you use that idea to create a native boxplot in power bi? Do you? 😏😏 Thanks a lot!
@HowtoPowerBI
2 жыл бұрын
How about using error bars? kzread.info/dash/bejne/ZaelscSCcrifcZs.html
PowerBI SypherPK
@HowtoPowerBI
2 жыл бұрын
hahaha just with 400x smaller channel 😅
Wrong dax
@HowtoPowerBI
11 ай бұрын
Works for me as you can see in the video 😅
@CasperLind1
11 ай бұрын
@@HowtoPowerBI I don't know man, but i get the concept and still try to do it xD i just can't follow your video 1 to 1