Creating a Gantt chart based on multiple conditions

[Anonymous]

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?

[mahoneypat · Accepted solution]

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

[Anonymous]

This worked perfectly, thanks!

[amitchandak]

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

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

[Anonymous]

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