Project Heatmap

[jamesclark458]

Hi Community,

I am looking for some help but haven't managed to find the right thread on the forum.

I have been asked to create a Heatmap to show how many projects we have in any given Year, Month and Week.

In PowerBI I have a data source (PPM Projects). In this table I have three columns that I think would need to be used: 

Project Start Date

Project End Date

Project ID

Essentially what I need to build either using one of the custom visuals, or by using the Matrix visual is a Heatmap that counts the number of distinct Project IDs and counts them across a calendar based on where the start and end dates are. 

So for example, if I had three projects (dates in UK format dd/mm/yyyy)

Project 1: Starts 01/01/2024 Ends 31/03/2024

Project 2: Starts 01/02/2024 Ends 31/03/2024

Project 3: Starts 01/03/2024 Ends 30/04/2024

The heatmap would count 1 in January, 2 in February, 3 in March and 1 in April. Thus January and April being the lowest values and March being the highest value. 

The visual would then be customisable so I can set the relevant colour schemes for highest and lowest values using some form of conditional formatting.

I have tried playing with Deneb, Calendar by Datanau and the native Matrix visual but haven't cracked it.

This may be something that isn't possible and I am living a pipeline dream, but if any forum is going to be able to help it is this one. 

Hopefully the ask is clear and thank you in advance!!!

Thanks

Jamie

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

[jamesclark458]

Hi @lbendlin 

Thank you for the tip.

I have put some sample data in the table below. For ease, I have put all the data into a single month. 

Project	Start Date	End Date
Project A	01/01/2024	31/01/2024
Project B	10/01/2024	25/01/2024
Project C	05/01/2024	20/01/2024

In terms of the expected outcome, I have provided a straightforward view of what I hope to achieve: a calendar view, in this case January 2024, that shows in a heatmap style where we have the lowest and highest number of projects occuring simultaneously. 

I hope this provides a bit more clarity to my ask.

Thank you,

Jamie

[lbendlin · Accepted solution]

how many projects we have in any given Year, Month and Week.

What's your definition of "week"  and how does it relate to your definition of "month" ?

see attached

[jamesclark458]

Hi @lbendlin 

Thank you so much for sharing this. It has worked a treat. 

In terms of the definitions of month, this is purely just the calendar month; Jan, Feb, Mar etc.

For weeks, this is Monday - Sunday, however given the level of detail we have been able to get from the solution you have provided, I think we have the granularity required and users can filter on the dates they need.

Thank you again for all your help 🙂

Jamie

[lbendlin]

yeah, mixing weeks and months is ill advised at best and misleading at worst.