Creating a Visual Calendar in Power BI

[dedelman_clng]

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 1^st 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
  • Week of Month
• Columns
  • Day Name
• Values
  • Day Number (use the Minimum aggregation)
  • Measure(s) to display

For a multiple month calendar, use the following columns in the matrix

• Rows
  • Year
  • Week Num
• Columns
  • Day Name
• 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

[D4imi0n]

Hi @dedelman_clng,

thank you for your solution. It works perfectly fine in my TimeSheet Report.

Cheers,

Jeffrey

[Anonymous]

solved

[dedelman_clng]

It has come time my attention (since I'm the one who tried it), that the setup instructions need to be modified if your date table is retrieved from an external source in a DirectQuery report (as opposed to a pure DAX date table made by CALENDAR or CALENDARAUTO).

Here are the changes:

Week of Month =
VAR __currWeek =
    WEEKNUM ( DimDate[Date], 1 )
VAR __startWeek =
    WEEKNUM ( DATE ( [Year], [Month Number], 1 ), 1 )
RETURN
    __currWeek - __startWeek + 1

Day Name = SWITCH(DimDate[Day of Week], 
   1, "Sunday", 
   2, "Monday", 
   3, "Tuesday", ...etc

Adjust the number/name pairs to fit your requirements.

This same feature could also be accomplished by creating a new table (using Enter Data), putting the 7 number/name pairs in there, and doing a 1-to-many join to DimDate on DimDate[Day of Week]. Then for the visual you would use the name from the new table (also, the step of sorting "Day Name" by "Day of Week" takes place on the new table instead of the calendar table).

Day Label =
IF (
    DimDate[Day Number] = 1,
    IF (
        DimDate[Month Number] = 1,
        "'" & YEAR ( DimDate[Date] ) - 2000 & " " & 
           DimDate[Month Abbreviation] & " " & DimDate[Day Number],
        DimDate[Month Abbreviation] & " " & DimDate[Day Number]
    ),
    "" & DimDate[Day Number]
)

This code returns "'21 Jan 1", "Feb 1" (and the day number for all dates not the first of the month). Alter to fit your requirements.

Please comment if you have any questions about my methods or if you come across any other deviations.

[Serdet]

Hi,

Thanks for creating this post within the community. I can't seem to find your PBIX file attached.

Please can you post it?

Many thanks,

E

[Amardeep100115]

Could you please share PBIX file 

[jklein]

This is very useful thank you @dedelman_clng. I have a slight issue with how the calendar recognizes the dates towards the end of the previous month and start of the current month. I am doing a weekly time tracking dashboard for my company. This is what the error looks like:

October 31st is showing up in the next row, how can I move it up?

[dedelman_clng]

Hi @jklein -

Looks like you are using the structure for a single month instead of multiple months. See below. Try the bolded one and that should get you what you need.

For a single month calendar, use the following columns in the matrix

• Rows
  • Week of Month
• Columns
  • Day Name
• Values
  • Day Number (use the Minimum aggregation)
  • Measure(s) to display

For a multiple month calendar, use the following columns in the matrix

• Rows
  • Year
  • Week Num
• Columns
  • Day Name
• Values
  • Day Label (use the First aggregation)
  • Measure(s) to display

Hope this helps

David

[jklein]

@dedelman_clng I cant seem to get it after trying and retrying your soluiton. Would you be willing to post your file so we could see exactly what you did?

[dedelman_clng]

All - please see this link for the sample pbix (also note it is around 18 months old so several major versions of Power BI ago).

Sample Calendars PBI file on github 

[jklein]

@dedelman_clng Thank you sir! I realized what the problem was, Day Number of Week was set to:

Day Number of Week = WEEKDAY(DimDate[Dates], 2)

When it should have been set to 

Day Number of Week = WEEKDAY(DimDate[Dates], 1)

This was causing it to reconize Sunday as part of the previous week instead of a new week. Small mistake but glad I got it. 

This is an awesome visual and I appreciate your contribution to this post.

[Divya_M]

Hi @dedelman_clng , I'm trying to replicate exact calendar view as a matrics table but I got struck in Date lable step and didn't get understand the "" in the below Dax

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]
)

[dedelman_clng]

@Divya_M -

"" & DimDate[Day Number]

is just a way of forcing the output to be text instead of numeric. It is an empty string that is concatenated to the day number via the & operator, so result is of type text no matter which part of the IF statements is true.