PROJECT MANAGEMENT in Power BI? Create a GANTT CHART from a bar chart | NO CUSTOM VISUAL solution
Вставка
- Опубліковано 2 лип 2024
- 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 UA-cam: 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 - Наука та технологія
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
Very creative! Always amazed how much you are able to get out of the standard visuals
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!
same!
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!
Everytime I see your video's I am left totally awestruck.. Hatts off to you
The best PowerBI content by far. So practical and looks good.
thank you so much!!!!
Totally agree
Yeah absolutely ✌
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
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!
Happy to hear this Tani!
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
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)
Amazing video! Thank you for sharing these tricks. 👍
Awesome as always Bas, ty for this piece of art, hehe.
This is brilliant! Thank you!
Fantastic!!!! It is my favorite UA-cam channel!
Yey 🎉🎈☺️😄
Great video, thanks!!! Maybe you can include the critical path in a next video.
Always Awesome content..Thanks for sharing
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
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
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)
@@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.
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 man, you are awesome.
muchas gracias, excelente aporte
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. 😀
that's amazin 😁 thanks!
Thank you so much, Bas. That helped me a lot.
Awesome glad to hear it helped 😎😃
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
Good work I completed this Gantt chart thanks to your video
I didn't understand why the data labels for the CL - Month1 was named September if Var Month1 = EOMONTH(OverallStartDate,0)+1
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! 😀
This is great! Thanks a lot!!!
Glad it was helpful, Dirk!
Wow amazing, well done Bas!
Thanks a lot Giavo! :)
Fancy Bas, nice - thanks!
Thank you for what you do! 🙏
thanks for watching!😊
Amaziiiiiiiiiiiiiiiiiiing my bro!
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.
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)).
You are amazing .....
Wonderful! Would never have thought this possible. Any ideas on how to get milestones into the chart? Would I have to overlay charts? 🤔
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
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 ?:)
Great video ! Thx
:) thanks
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?
Great video. Thanks for sharing.
You are welcome!
Amazing
super good!
thx Matthias!!! 😀
hello Bas , thank you for educating us. Small help ...if you can share both the data tables used to create the chart.
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
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!
To resolve this error- after defining VAR Result, you need to return Result. i.e. last line of dax would be return Result.
Facing same error sir can you please elaborate...?
@@exhaustking9131 - I had the same problem, but writing "RETURN Result" on the last line fixes the problem!
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?
Hello Bas, How can I show a constant line for the Start of the Year as oppose to EOM?
great info. what if one of the projects' start date is empty? i tried your formula but gives me bad overallstartdate. thanks
Thanks!
Thank you Yvette for the support!
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?
for sure, you would need to adjust the measure to show a 1 for the relevant data ranges
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 :-)
good point!!! tricky one though 😁
@@HowtoPowerBI You are our Power BI Master! I am sure you will handle it! :-)
@@marcindabrowski1742 🤣 no pressure haha
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: ua-cam.com/play/PLBE7Yqq2lkv6-mN1EK2_ry7bai_zpTQ_2.html
@@HowtoPowerBI any luck ?
Good work thanks
thanks a lot alassane! 😊
Bus, can you please add if project has more than 3 type of duration on the same bar.... ? Thanks
Great video, as always! Do you have any recommendations on how to add MM/YY to the X-axis instead of Duration in Days?
maybe better to go for this option if you need mm/yy ua-cam.com/video/QugkBqc-jIs/v-deo.html
Amazing 😍
THANKS! 😁
What if we want to show the dates on the x axis instead of Duration. and the same on the zoom slider.
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?
Thanks
Hi Rajneesh, thanks a lot for the support!
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 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???
Amazing stuff. 👏
How about using dates instead of duration. Think that'll work?
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 😉)
@@HowtoPowerBI True True.
Looking forward to that video.
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
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
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
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?
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
very nice
thank you Nagaraj!
Where's the overall start date measure coming from? I seem to have missed that :)
Valeu!
Thank you so much for the support Lean!
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?
I have the same error :( You solved?
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.
Hellooo, How we put gradient color in single bar charts????
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.
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
still works ... download the file (see description) and open it in the latest version
Awesome walkthrough but how can I add a holiday in the Gantt chart?
You can probably add a Holiday table a your project dataset
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?
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.
@@HowtoPowerBI Unfortunately, no squiggly lines. I just get an error "The syntax for ')' is incorrect.
I figure it out. I needed to end the syntax with RETURN Result
@@lindakeefe9591 I think it's because he has a dimTable (that I didn't have while I was trying to follow-this)
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.
I'm getting a syntax error
@@krishanmw1 Same here!
The syntax for ')' is incorrect.
@@tobyheikkila6818 I have the syntax error too - did you manage to fix?
@@justmejustme4444 I'm getting this error too - did you have any luck fixing it?
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?
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!
thanks for watching Shanee! Would like to help, but not sure what you mean with a ")" error. Could you give more details?
@@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.
@@shaneemadden135 after the return statement you have "))." why? and why do you have (DAX( at the beginning ?
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!
@@shaneemadden135 did you figure out a solution. I am having the exact same problem
What It Project A has more than one occurrence?
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))))."
I went ahead and just used the gannt chart Microsoft came up with. You can search for it in the getvisuals section
after defining Result you need to return Result. hence last line of Dax would be return Result
@@stavanparikh1668 Thank you.
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?
the fx button only shows in pbi version aug 21 or later
What is the dimDate Measure and how was it calculated?
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.
thx for answering Rubén
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?
can you copy paste your measure here ... check also if you have RETURN result in the end ..
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?
Could you post the formula and error?
@@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.
@@bailjr you forgot to write result after return and i m not sure what the **** are here?
@@HowtoPowerBI It worked when I added RETURN. The *** is the name of my spreadsheet...I wanted to protect the name of the document. Thanks!
@@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?
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
There is a typo somewhere in your code
It should be: RETURN Result. Video cut off the last word.
Hi, can you add multiply milestones in this gantt chart ?
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: ua-cam.com/video/QugkBqc-jIs/v-deo.html )
@@HowtoPowerBI Thanks for your reply, but we wanted a situation we can see each miletones , and there are many milestone .. thanks
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)).
You are missing at the end: return result
@@HowtoPowerBI THANK YOU!
Can you use that idea to create a native boxplot in power bi? Do you? 😏😏 Thanks a lot!
How about using error bars? ua-cam.com/video/3sqObIBU9A8/v-deo.html
what is dimdate?
ua-cam.com/users/shortsMChjMrjgn1Q
How can we create a stacked can’t chart?
Lol 😀
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?
PowerBI SypherPK
hahaha just with 400x smaller channel 😅
Wrong dax
Works for me as you can see in the video 😅
@@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