It’s pretty sad that we don’t have a built-in calendar visualization in Power BI, and the custom visuals in the marketplace don’t have everything I need/want for my own internal reporting…so I decided to experiment a little and see how close I could get with the standard graphs that are available.
What you’ll need
Before you get started, you’ll have to make sure that you have a Date entity or table with at least the following attributes:
- Numeric day of the month (1, 2, 3, etc.)
- Day of the week name or abbreviation (Mon, Tue, Wed, etc.)
- Week of the month or year (1, 2, 3, etc.)
Other than that you’ll just need the measures you’d like to display in the calendar, and you’ll be ready to rock!
Let’s do it!
First, add the matrix visual to your page in Power BI desktop and filter it (or the page) for a specific month. It gets a little messy if you want to view multiple months at the same time, and I would recommend using a slicer or some other filtering mechanism to restrict this “calendar view” to a single month.
Add an attribute for the week to the Rows section of the matrix. I’m using the Week Ending date below, but you can also use any other attribute that will uniquely identify the week within a month.
Now that we have a row for each week of the month, we can get a column for every day of the week by adding the Day of the Week attribute to the Columns section of the matrix.
The next thing I’d like to be able to show is the day of the month, so let’s add that attribute to the Values section of the matrix. By default, the matrix will have to aggregate this attribute because we are trying to show it as a value…but that’s ok because it will be distinct within the Day Of Week attribute we already added as a column group. I opted to use Max as the aggregate below, but Min will also work.
The last item in terms of data elements would be the measures we’d like to show. I’ve added a few below…one each for Booked Hrs, Billable Hrs and Non-Billable Hrs.
Lipstick on the pig
The matrix looks pretty messy at this point and it’s hard to believe that this can look like a calendar, but with some fancy formatting footwork we can make it look a lot better!
Remove the subtotals and switch the values to the rows, and this is starting to look a lot closer to what we want…
Now remove the banded row color and stepped layout of the Row Headers. This will eliminate the empty row at the top by placing the Week Ending date next to the other row headers, which in turn will make it easier to hide this section in the matrix. You can also change the text colors of this section to White if you like, but it’s not really necessary.
The last few formatting changes to get it really close:
- Hide the row headers by resizing the columns and making them as small as possible. This will effectively hide the first two columns.
- Increase the row padding in the grid to space things out a little more.
- Remove all grid borders.
- Left-align the day of the month column values, and change the background and/or text color so it stands out.
- Choose a uniform background color for all rows.
Play around with the other formatting options until you get the exact view you are looking for. Below is what I ended up with…I think it’s as close as it’s going to get.
