cancel
Showing results for
Did you mean:
Helper III

## Creating a Gantt chart based on multiple conditions

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

 Project Start Finish A 9/1/2021 9/5/2021 B 8/25/2021 9/20/2021 C 9/10/2021 9/20/2021 D 8/5/2021 8/31/2021 E 9/25/2021 10/31/2021 F 9/5/2021 11/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:

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
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.

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!

4 REPLIES 4
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.

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!

Helper III

This worked perfectly, thanks!

Super User

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

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

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!
Helper III

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

Announcements

#### Launching new user group features

Learn how to create your own user groups today!