Cluster Bar Chart Showing Years Based on Start and End Date of Activities

[kamini]

Hi folks,

I'm trying to create a Cluster Bar Chart that shows years (only) at the X-Axis but the Bars will show the information based on different activities Start and End Date. Below is an example of what I need to establish.,

So, as above shows, I'm trying to have the Bar Chart and the left activities information in the same visual. 

I greatly appreciate your time.

Thank you,

[lbendlin · Accepted solution]

you can play with the settings yourself.

Column = 
var mind = minx(all(Projects[Start Date]),[Start Date])
var maxd = maxx(all(Projects[Finish Date]),COALESCE([Finish Date],TODAY()))
var svg = 
"data&colon;image/svg+xml;utf8,<svg xmlns='http://www.w3.org/2000/svg' viewBox='0 0 " & format(maxd-mind,"#") & " 65'>
<rect fill='#3d5599' x='" & format([Start Date]-mind,"#") &  "' width='" & format(COALESCE([Finish Date],TODAY())-[Start Date],"#") &  "' height='100%'></rect>
<rect fill='#001f3f' x='" & format(dt"2023-01-01"-mind,"#") &  "' y='0' width='2' height='35'></rect>
<rect fill='#001f3f' x='" & format(dt"2024-01-01"-mind,"#") &  "' y='0' width='2' height='35'></rect>
<rect fill='#001f3f' x='" & format(dt"2025-01-01"-mind,"#") &  "' y='0' width='2' height='35'></rect>
<text font-size='x-large' fill='#0074d9' x='" & format(dt"2023-01-01"-mind,"#") &  "' y='60'>FY23</text>
<text font-size='x-large' fill='#0074d9' x='" & format(dt"2024-01-01"-mind,"#") &  "' y='60'>FY24</text>
<text font-size='x-large' fill='#0074d9' x='" & format(dt"2025-01-01"-mind,"#") &  "' y='60'>FY25</text>
</svg>"
return svg

[lbendlin]

I'm trying to have the Bar Chart and the left activities information in the same visual. 

That's a bold goal. Is it truly necessary?  If yes, you will have to use SVG images inside the table column.

[kamini]

Unfortunately, it is necessary demanded by the client. 

[lbendlin]

Please provide sample data that covers your issue or question completely, in a usable format (not as a screenshot).

Do not include sensitive information or anything not related to the issue or question.

If you are unsure how to upload data please refer to https://community.fabric.microsoft.com/t5/Community-Blog/How-to-provide-sample-data-in-the-Power-BI-...

Please show the expected outcome based on the sample data you provided.

Want faster answers? https://community.fabric.microsoft.com/t5/Desktop/How-to-Get-Your-Question-Answered-Quickly/m-p/1447...

[kamini]

Name	Start Date	Finish Date	Status
Assist Payment Processing Deleting Old IBML Batch Images	3/1/2023	3/31/2023	Completed
Build FFX 5.0 Machine for UCP	10/24/2023	11/24/2023	Not Started
Create Reports forLegacy Scanning	3/1/2023		Completed
Decommission OLD Environment	10/4/2023	11/16/2023	In Progress
Enable Encryption for Databases	3/6/2023		Completed
Fix Form Issue	3/1/2023	4/28/2023	Completed
Group Policy Cleanup	2/24/2023	1/19/2024	In Progress
IBML Application Upgrade	3/1/2023	12/22/2023	On Hold
Migrate Users From WebEx to MS Teams	7/12/2023	1/19/2024	Not Started
Resolve Check Identification Issue	3/6/2023	4/14/2023	Completed
Perform system A Upgrade	9/12/2022	10/13/2022	In Progress
Perform system B Upgrade	10/13/2022	11/13/2022	In Progress
Perform system C Upgrade	11/14/2022	12/13/2022	In Progress
Perform system D Upgrade	12/15/2022	1/13/2023	In Progress
Maintain Annapolis	9/5/2023	12/14/2023	In Progress
Maintain Baltimore	10/5/2024	12/11/2024	Not Started
Clear ATCC from Gov Delivery	8/10/2023	8/16/2023	Completed
Perform Audit Findings and Remediations	8/14/2023	8/28/2023	Completed
Apply IBML SCS  Legacy DBs	3/1/2023		Completed

[kamini]

So, basically, I'm trying to show the list of activities (Name in sample data) their Start Date and Finish Date, but very importantly, show the right hand Bar Chart showing per year the Duration of activities (Bars). 

Thank you for your time.

[lbendlin]

Your original picture has fiscal years and colors?

[kamini]

The colors per year would be helpful (+Legend) but most importantly, the Bars of the Chart (basd on Duration) and the X-AXIS showing the years is the most important part of this product. Thank you!

[lbendlin]

Here's a basic idea of how this would look like

[kamini]

That is great and pretty much what I was asking. If you have any recommendations for adding the FY22 FY23 on the bottom or top of the Bar chart based on the Durations, please let me know.🤔 Thank you,

[lbendlin]

what are the start dates for these?

[kamini]

The Start Date is the same as each Task's Start Date shown as a part of the Sample Data. Thank you!

[lbendlin]

I need to know when FY22 and FY23 begin.

[kamini]

Ah, Ok, 

All years starts in January 1, 2022 and ends Dec 31, 2022. The New Year will be Jan 1. 2023. It is based on the US calendar year. 

Thank you

[lbendlin · Accepted solution]

you can play with the settings yourself.

Column = 
var mind = minx(all(Projects[Start Date]),[Start Date])
var maxd = maxx(all(Projects[Finish Date]),COALESCE([Finish Date],TODAY()))
var svg = 
"data&colon;image/svg+xml;utf8,<svg xmlns='http://www.w3.org/2000/svg' viewBox='0 0 " & format(maxd-mind,"#") & " 65'>
<rect fill='#3d5599' x='" & format([Start Date]-mind,"#") &  "' width='" & format(COALESCE([Finish Date],TODAY())-[Start Date],"#") &  "' height='100%'></rect>
<rect fill='#001f3f' x='" & format(dt"2023-01-01"-mind,"#") &  "' y='0' width='2' height='35'></rect>
<rect fill='#001f3f' x='" & format(dt"2024-01-01"-mind,"#") &  "' y='0' width='2' height='35'></rect>
<rect fill='#001f3f' x='" & format(dt"2025-01-01"-mind,"#") &  "' y='0' width='2' height='35'></rect>
<text font-size='x-large' fill='#0074d9' x='" & format(dt"2023-01-01"-mind,"#") &  "' y='60'>FY23</text>
<text font-size='x-large' fill='#0074d9' x='" & format(dt"2024-01-01"-mind,"#") &  "' y='60'>FY24</text>
<text font-size='x-large' fill='#0074d9' x='" & format(dt"2025-01-01"-mind,"#") &  "' y='60'>FY25</text>
</svg>"
return svg

[kamini]

Would you mind sending me the pbix file so I can continue the customization from there. Thanks so much!