Calendar Heatmap Visual in Power BI: A macgyvered approach
One of the common ways to detect or visualize seasonality in data is using a line chart or a variant of a line chart. In this post I show an alternative chart type to visualize seasonality or patterns in data over time and you can create one in Power BI.
UPDATED: 08 Oct 2022
Calendar Heatmap
Heatmaps are one of the commonly used visualizations to show patterns for date or time-based data. A calendar heatmap is basically a heatmap with a layout similar to a calendar structure. This kind of heatmap makes it easy to spot patterns at the month level, week level, and day level all in one glance. If the week structure (and calculation) is modified, it can also be used to compare weekdays vs. weekends and determine if the metric of focus is high on weekdays or weekends.
I first came across this visual in the book “Data Points: Visualization That Means Something” by Nathan Yau (good book on data visualization). In a section of the book, the author explores different options for visualizing time series data and compares their strengths and weaknesses. Having seen this I thought to myself, “how can I create this in Power BI?”
How to make one in Power BI
In Power BI, there is no native visual that lets you create this out of the box, however there are custom visuals that can give you one. An example is the Calendar by Tallan custom visual. But I like to “hack” native visuals or as Kurt Buhler calls it macgyvered visuals. I learn in the process. You can also probably do this using Deneb or Charticulator but I’ve gone for the chart hacking.
The Build: Getting the structure
The first puzzle to figure out for creating most of the custom-made visuals in Power BI (also tableau) is how can you get it on an x and y axis or on rows and columns in this case. We need to create a structure that would hold the calendar and this is going to be dependent on what layout you are going for, a 4x3, 3x4 or 6x2 grid. I take inspiration from a technique done in Tableau by Andy Kriebel.
Essentially, we want to break the dates into a grid, I’ve chosen a 4x3 grid, where we have each row containing a quarter of the year and the columns represent a month in the quarter split by day of the week. Then in a matrix we can match the parts together with DAX.
To create this in Power BI we need a Dates table that would have the following columns in addition to the standard date columns:
- Day of Month
- Quarter of Year
- Week of Month
- Day of Week, Day of Week Number
- Month Group; which groups each month into the columns. E.g Jan, Apr, Jul, Oct as group 1.
These columns are easier to create using Power Query. However, you could also create them in DAX.
We also need to create a disconnected table that could create the skeleton of the visual. This table would have the following columns, Week Index, Day of Week Number and Day of Week. The Week Index would correspond to the Week of Month in the Dates table, and the other two columns, Day of Week Number and Day of Week would correspond to the same columns in the Dates table.
The Build: Working in Power BI
The Date Table
Creating the Dates table can be done anyway you want, SQL, DAX, Power Query. Here’s the sample Dates table I’m working with. Week Index is the Week of Month, Day is the day of month and Month Group is as described above.
The Date Heat Table
For the disconnected table, which I’m going to call Date Heat, I chose to use DAX to create it. Basically, what we do is generate a list of numbers from 0 to 5 which is the Week Index and then for each of the Week Index we have a list of all the days of the week and an additional row which we would use as a “gap” to create a space between the months. We use numbers from 0 to 5 because, typically, a month would have at most, 5 weeks.
The Date Heat table looks like this.
Now, that we have the basic building blocks ready, we can continue with the matrix visual. Add the Quarter column from the Dates table, and the Week Index column from the Date Heat table to the rows. Add the Month Group column from the Dates table, and Weekday column from the Date Heat table to the column of the matrix visual. Also create a dummy measure that just has the value, 1, so we can see the result. On the format pane, change the Stepped layout on the row headers to ON so you have all the rows nicely expanded in just one column.
Already we see the visual taking shape. And you can see a couple of things. You see the essence of the “gap” row and also you have a better idea of what might be the next steps. Somehow we need to “map” the columns coming from the Date Heat table to the corresponding columns on the Dates table.
The Date Heat table has three columns which effectively should correspond to the Week of Month (Week Index), Weekday and Weekday Number columns of the Dates table. We can move the filter from the Date Heat table as filters applied to the Dates table using DAX. Let’s do that and calculate what day of the month should be shown on the matrix.
Day of Month =
CALCULATE (
MIN ( Dates[Day] ),
TREATAS (
'Date Heat',
Dates[Week Index],
Dates[Weekday Number],
Dates[Weekday]
)
)Using TREATAS, we map all the columns to the Dates table and calculate MIN of the date. That gives us the following result filtered down to a single year. In this example, 2020 is the year.
To make the gap the 0 Week Index visible and the “gap” on Weekday, you have check “Show values with no data” on the rows and columns of the matrix.
It’s almost complete. The final thing is to have the month names at the top of each month. You can decide to use a text box to do that but it won’t be dynamic. To make it dynamic, we would include it in the measure. The month name would stay at the point where the Week Index equals zero and Weekday is Wednesday. So we incorporate that logic in the measure.
Day of Month =
VAR DayNumber =
CALCULATE (
MIN ( Dates[Day] ),
TREATAS (
'Date Heat',
Dates[Week Index],
Dates[Weekday Number],
Dates[Weekday]
)
)
VAR IsLabelWkIndex =
SELECTEDVALUE ( 'Date Heat'[Week Index] ) = 0
VAR IsLabelDay =
SELECTEDVALUE ( 'Date Heat'[Weekday] ) = "Wed"
VAR ShowMonthTitle = IsLabelDay && IsLabelWkIndex
VAR Result =
IF ( ShowMonthTitle, SELECTEDVALUE ( Dates[Month] ), DayNumber )
RETURN
ResultPlacing the modified measure into the matrix produces this result.
The solution is pretty much done at this point. What is left is just formatting and presentation. Hiding the row and column headers, using conditional formatting to create the background fill of the matrix and adding a legend.
The DAX pattern remains the same for whatever metric you want to show. Substituting MIN( Dates[Day] ) with any measure gives the expected result. A pattern that can be repeated across different measures means there is an opportunity to use Calculation Groups. With calculation groups you can apply the pattern to any measure and have the heatmap for that metric. You can further expand on this using field parameters and give the report user the option to select a measure and with the calculation group applied, it works beautifully. Hopefully, I’ll write a short article soon, explaining how to go about it. Until then, if you would like to see an example of this already implemented, check this out.
Final Thoughts
It was interesting trying to figure this out. I hope you find this useful. If you do, share it with Power BI folks who might also find it interesting.
If you would like to improve on some things, connect or collaborate on Power BI related stuff, reach me on Twitter or LinkedIn. Check out my portfolio for some other interesting stuff.
Check out an interesting implementation of this by Greg Philps using Deneb. Pretty awesome!
Don’t forget to subscribe to
and join our Power BI community
