Heat Map from Matrix, using Months as Columns, across several years?

[SevsBo]

I'm trying to create a heat map of interactions across a time-period that the user would specify. The heat map part is quite straightforward, but I am struggling to get the dates to work.

I want to show the timeline of interactions across the columns, but if I put in the hierarchical Date table I am using to filter the results, I get January to December as column headers. That seems OK, until I select a time range on the filter that is longer that the current year. At that point Power BI starts adding values from the new month to the current Jan - Dec columns, aggregrating the results for last year's Jan to this year's for instance.

Is there a way to make a matrix / heat map where the month columns are bound to the year they're in, so if the user extends the time range beyond the current year, the column headers might look something like: (2023) Jan, (2023) Feb.... (2024) Jan, (2024) Feb) etc?

[SevsBo]

Hi @Anonymous 

Apologies for the belated reply. Here is some more detail. The two screenshots below both have three elements: 

1. A small table of data, in this case simply showing dates and a count of contact_sk through time
2. A Matrix / heat map
3. A column chart showing the distribution of contact_sk over time

Both the matrix heat map and the column chart use the same data, but only the column chart shows it in a way that takes different years into account.

In the first screenshot, the time range is set to only 1 Jan 2023 to 31 Dec 2023, and both the column chart and heat map show results as I would expect. But when I expand the time range to include 2022 as well, on the bottom screenshot, only the column chart shows the desired result. The heat map condeses the months, so it sums the counts of contact_sk from January 2022 and January 2023, etc, and I cannot find a way to enable date hierarchy in the same was as I would in the column chart.

I hope the below screenshots explain my predicament:

Screenshot 1 - only 2023

Screenshot 2 - 2022 & 2023

I get the same problem using the official Microsoft heat map plug in, the Table Heatmap 3.50:

Screenshot 3 - Heatmap only 2023

Screenshot 4 - Heatmap 2022 & 2023

[Anonymous]

Hi @SevsBo ,

Can you please share a pbix or some dummy data that keep the raw data structure with expected results? It should help us clarify your scenario and test to coding formula.

How to Get Your Question Answered Quickly  

Regards,

Xiaoxin Sheng