cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Sach
Helper III
Helper III

Creating a Gantt chart based on multiple conditions

I have a source data Excel that looks like this. Call it ProjectData.xlsx

 

ProjectStartFinish
A9/1/20219/5/2021
B8/25/20219/20/2021
C9/10/20219/20/2021
D8/5/20218/31/2021
E9/25/202110/31/2021
F9/5/202111/21/2021

 

It's a project start and end date table, which I get from a different department in our company. We get this every week, and using this, we calculate an Excel gantt chart of sorts, based on several conditions. Finished Excel looks like this:

 

ExcelScreenshot.png

 

Notes:

  • Cell D1 is the today's date.
  • Cells E1, F1 etc are advanced by one day. Here I set the time period for 2 weeks, but in reality, it would go on for a year. Essentially, D1 + 365 days.
  • The "Long", "Short", and "-" is decided using the following criteria:
    • If the date in the column (D1, E1 etc.) is between [ Start ] and [ End ],
      • If the project duration is <= 10 days, cell content is "Short"
      • If the project duration is > 10 days, cell content is "Long"
    • Else
      • Cell content is "-"

 

For example, the formula for D2 would look like this:

 

 

=IF(AND(D$1 >= $B2, D$1 <= $C2), IF($C2 - $B2 <=10, "Short", "Long"), "-")

 

 

 

Now, only using the ProjectData.xlsx which I get from the aforementioned other department, I would like to recreate this in PowerBI. I know that to simply show project durations, I could use a Gantt chart controls, but I don't think I can accomplish something like this that has multiple conditions.

 

So, what's the best way to go about creating this (presumably using a Matrix visual?) in PowerBI?

1 ACCEPTED SOLUTION
mahoneypat
Super User
Super User

The approach used here is very similar to the one suggested by @amitchandak.  But it is more specific to your post.  You can make a matrix visual with your data, a Date table that goes from Today to Today() + 365, no relationship between the two tables, and these two measures - one for the value and one for the conditional formatting.

 

mahoneypat_0-1629909501288.png

GanttLabel = var thisdate = SELECTEDVALUE(Date2[Date])
var startdate = MIN(Projects[Start])
var enddate = MIN(Projects[Finish])
return IF(thisdate <= enddate && thisdate>=startdate, IF(DATEDIFF(startdate, enddate, DAY)>10, "Long", "Short"), "-")
 
GanttColor = SWITCH([GanttLabel], "Long", "Yellow", "Short", "Green")
 

Pat

 

 





Did I answer your question? Mark my post as a solution! Kudos are also appreciated!

To learn more about Power BI, follow me on Twitter or subscribe on YouTube.


@mahoneypa HoosierBI on YouTube


View solution in original post

4 REPLIES 4
mahoneypat
Super User
Super User

The approach used here is very similar to the one suggested by @amitchandak.  But it is more specific to your post.  You can make a matrix visual with your data, a Date table that goes from Today to Today() + 365, no relationship between the two tables, and these two measures - one for the value and one for the conditional formatting.

 

mahoneypat_0-1629909501288.png

GanttLabel = var thisdate = SELECTEDVALUE(Date2[Date])
var startdate = MIN(Projects[Start])
var enddate = MIN(Projects[Finish])
return IF(thisdate <= enddate && thisdate>=startdate, IF(DATEDIFF(startdate, enddate, DAY)>10, "Long", "Short"), "-")
 
GanttColor = SWITCH([GanttLabel], "Long", "Yellow", "Short", "Green")
 

Pat

 

 





Did I answer your question? Mark my post as a solution! Kudos are also appreciated!

To learn more about Power BI, follow me on Twitter or subscribe on YouTube.


@mahoneypa HoosierBI on YouTube


View solution in original post

This worked perfectly, thanks!

amitchandak
Super User
Super User

@Sach , refer if my attempt of putting in matrix can help

Matrix as Project plan Visual: https://youtu.be/R25QoiyoSVs

Did I answer your question? Mark my post as a solution! Appreciate your Kudos!! !!
Dashboard of My Blogs !! Connect on Linkedin

Learn Power BI Beginners !! Advance Power BI Concepts !! Power BI For Tableau User !! Learn Power BI in Hindi !!
Proud to be a Super User!

Thank you for the response, but this doesnt seem like what I'm looking for.

Helpful resources

Announcements
UG GA Amplification 768x460.png

Launching new user group features

Learn how to create your own user groups today!

November Power BI Update 768x460.png

Check it Out!

Click here to read more about the November 2021 Updates!

M365 768x460.jpg

Microsoft 365 Collaboration Conference | December 7–9, 2021

Join us, in-person, December 7–9 in Las Vegas, for the largest gathering of the Microsoft community in the world.