Everyone is used to seeing date/time series data in a “linear” fashion – column charts, line charts, etc. The X-axis is the date, the Y-axis is the value. When we use a continuous X-axis, Power BI tends to limit the number of data points shown on the axis, making focusing on one particular day difficult (a date slicer can be used to only show that particular day, but then the line chart becomes a dot). The column and line charts are also not intuitive to be able to compare days of the week against one another, or the same day of the week across multiple weeks/months.
However, a calendar layout – the same format used from old-style desk planners wall hangers to Outlook – could solve some of the shortcomings of the standard visuals. Not only will we be able to look back in the past, we can even use it to look into the future for schedules, forecasting, etc.
This post looks at how to build a visual calendar that still has all of the power of Power BI. We will look at creating both a single month calendar and a calendar that spans multiple months (even across years).
Here are some examples for how this visual will look and work:
Single month
Single Month w/ Day of Week highlighted
Multiple months
Multiple months with single day selected
Multiple months with week selected
Data Model
To build a visual calendar, we will need at least the following
- Calendar Table/Date Dimension
- Fact table with at least a daily grain
Calendar Table
For the Calendar table, we can use CALENDAR() or CALENDARAUTO(); however, a table that can be accessed via Power Query is preferred due to some PowerQuery functions that are, in my opinion, easier to use than similar in DAX.
PowerQuery/M
Besides the usual date, year, month, quarter, etc., we will want the following columns to be added
"Week of Month" = Date.WeekOfMonth([Date], Day.Monday)
This will return a number 1-6. The second parameter “Day.Monday” indicates what day of the week is considered the first. This will be based on our use cases and/or business rules.
"Day Name" = Date.DayOfWeekName([Date])
This will return “Sunday”, “Monday”, etc. (we could do this in DAX as FORMAT(DimDate[Date], “dddd”) ).
PBI/DAX
Day of Week = WEEKDAY(DimDate[Date], 2)
Week Num = WEEKNUM(DimDate[Date], 2)
The second parameter on both of the above indicates which day of the week is considered the beginning of the week – see Weekday for more details. 2 indicates Monday will be day 1, and this needs to match with the “Week of Month” parameter from above.
Day Number = DAY( DimDate[Date] )
Day Label =
IF (
DimDate[Day Number] = 1,
IF (
DimDate[Month Number] = 1,
FORMAT ( DimDate[Date], "'YY MMM D" ),
FORMAT ( DimDate[Date], "MMM D" )
),
"" & DimDate[Day Number]
)
Day Label will return the day of the month. On the 1st the Month it will include the 3-character month abbreviation (Feb 1), and on Jan 1 it will include the last 2 digits of the year (’21 Jan 1). Modify the code as needed for business needs/regional settings.
The final preparation step for the date table is to do “Sort by column” on “Day Name“ and choose “Day of Week”.
Fact Table
Make sure the fact table has an active relationship to the Date/Calendar table. This visual can handle explicit and implicit measures, so prepare those as necessary.
Build the Visual
PBI Canvas
(NOTE: this demo was created in Version: 2.91.884.0 64-bit (March 2021), so your Power BI may look different)
We will use the built-in Matrix visual. Drag one to the canvas.
For a single month calendar, use the following columns in the matrix
- Rows
- Columns
- Values
- Day Number (use the Minimum aggregation)
- Measure(s) to display
For a multiple month calendar, use the following columns in the matrix
- Rows
- Columns
- Values
- Day Label (use the First aggregation)
- Measure(s) to display
A brief tangent about having a list as a measure
As mentioned, one use case for this visual calendar is to show a schedule of “events” per day, either in the past or in the future. To put together a clean list that has a new line for each item, we can use this pattern
List = CONCATENATEX(FactTable, FactTable[Items], "
")
Note that there is a “hard” carriage return in between the quotation marks. This was achieved in Power BI by pressing Alt-Enter between the quotations.
/End tangent
Formatting the matrix – Formatting Pane
(only showing values that deviate from default)
Grid
- Vertical Grid: ON
- Horizontal Grid: OFF
- Outline Color: recommend same color as Vertical Grid
Column Headers
- Outline: Top + bottom
- Auto-size column width: OFF
- Alignment: Center
- Word Wrap: OFF
Row Headers
- Stepped Layout: OFF
- Word Wrap: OFF
Values
- Show on Rows: ON
- Word Wrap: OFF
Subtotals
- Row subtotals: OFF
- Column subtotals: OFF
Field Formatting
- Day Label
- Background Color: a light gray like #E5E5E5
- Alignment: Center
- Apply to values: ON
- Measure(s)
- Background Color: suggest white #FFFFFF; may want different background if displaying multiple measures, but don’t re-use the background for Day Label
- Alignment, Display Units, Decimals: modify as desired
Visual Header
- Recommend turning it OFF, or at least not displaying the “drilling”-related buttons
Formatting the visual – on the visual
So now we see the calendar layout, but with these unsightly extra columns at the beginning of each line
These are simple to minimize. We put the cursor on the vertical line between “Week of Month” and the next column. The cursor turns into this:
We click on that vertical line and drag to the left until we no longer see anything in that column. We repeat the same for the “column” containing the Value names. If we’ve done everything correctly, all we have left is what looks like a calendar. We may need to widen/shrink the individual day of week columns to accommodate the measures and/or to fill out the frame of the visual (unfortunately, I am unaware of a way to set a specific column width in Power BI the way one can in Excel, so we have to “eyeball” the widths). Helpful hint: if we leave just a tiny bit of space for each column, we can click on the far left edge to filter the calendar down to the specific week.
There we have it – a calendar layout with all of the Power of Power BI. I have attached a sample report for further information. If you have any suggestions or ideas for improvements, please put them in the replies!
Good luck and happy visualizing!
David
Be the first to comment