cancel
Showing results for 
Search instead for 
Did you mean: 
dedelman_clng

Creating a Visual Calendar in Power BI

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

01-single month full.png

 

Single Month w/ Day of Week highlighted

02-single month wednesday.png

 

Multiple months

03-multi month full.png

 

Multiple months with single day selected

04-multi month Mar 4.png

Multiple months with week selected

05-multi month week.png

 

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
    • 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

06-visual header.png

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

07-expanded.png

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: 08-cursor.png

 

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

 

 

Comments

Hi @dedelman_clng,

 

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

 

Cheers,

Jeffrey

solved

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.

 

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

Polls
What is your favorite Power BI feature released this month?