Select Page

When you're managing your projects in Power BI, I can highly recommend the Gantt chart by MAQ software. You can download it for free and it is extremely easy to use. All you need to do is enter the name of your projects, the start date and the end date and you're good to go. Additional features are also available but not required. This could be an overview of your projects.

However, if you have many projects ongoing you want to add a visual that shows the amount of projects ongoing on at any given time. This requires some DAX: you need an additional table that records, for every day, which projects are ongoing. That table can be based on the projects table. The latter is just a table of your projects that has start and end date as columns.

In this scenario you would need to crossjoin your date table and your projects table. However, you need to be able to adjust the date table, making sure you only have rows out of your date table returned for dates at which a project is running. Enter GENERATE. This is a DAX formula that allows for just that; the second table (the projects table) is the result of evaluating it in the context of the first table (your date table).

This would be your code:
GENERATE (
SELECTCOLUMNS ( dim_Calender , "date2", [Date] ),
CALCULATETABLE (
Projects,
FILTER ( Projects, Projects[Start date] <= [date2] ),
FILTER ( Projects, Projects[End date] > [date2] )
)
)
)

I have also added a value to this data, which could be anything you might want to record: the amount of FTE required for a project, the expected RoI, anything…

After having created the second table the below dashboard can be created. This shows the amount of projects that are ongoing during a year and allows for filtering the other visuals on the canvas, or for drilling down on it to the months and then to an individual date.