Build a Gantt chart in Table format in PowerBI

[Raylou]

Good Day,

I hope this post finds you well.

I have an excel file where I want to enter a start and an end date in to fill cells according to the year and quarter for the particular start/end dates.

I also would like them to change colour depending on the status selection.

I also have number filled in cells that needs to calculate a total for each quarter as well.

I have seen plenty of examples where the duration days are used to calculate the dates and if one date or duration are changed then it updates all activities or tasks listed.

However, for me I just want to change dates and not worry about dependencies on the tasks.

I have tried Gantt chart in Power BI, however my company will not at this stage pay for licenses to use it. So my options are limited.

Hence I was wondering with some Power BI coding etc, how can I insert a table that will show me the above requirements?

A solution will be highly appreciated. My knowledge are very limited to get the data in the correct way in PowerBI to create a table to group data according to years, quarters, etc. And filling in columns according to the dates and status for conditional formatting.

Using Microsoft® Excel® for Microsoft 365 MSO (Version 2302 Build 16.0.16130.20882) 64-bit
Windows 10

Q1 date range is: 1 Jan - 31 Mar
Q2 1 April - 30 June
Q3 1 July - 30 September
Q4 1 October - 31 December

Attached the sample of how it looks in excel that I will or want to import into PowerBI. It would have even been an option to do it in Excel, however I am also not wining on how to do it.

Thank you very much!

 

[Monty]

Hello Ray

To achieve your requirements in Power BI, you can check these steps:

1. Import your Excel file into Power BI.

2. Create a new table with the desired structure: columns for start date, end date, status, and any other relevant information.

3. Create calculated columns to extract the year and quarter from the start and end dates.

4. Use conditional formatting to change the color of cells based on the status selection.

5. Create measures to calculate totals for each quarter based on the numbers filled in cells.

6. Use visuals such as tables or matrices to display the data grouped by year and quarter.

if you need further assistance!..let me know

 

[Raylou]

Hallo Monty,

Hope you are very well.

Thank for those steps.

I have managed to create something in Excel, but will also try this method in Power BI. I guess I will get stuck creating the correct measures hehehe. Will give you a shout when I get there.

Much appreciated! Enjoy the week!

 

[Monty]

That's great...