Showing results for 
Search instead for 
Did you mean: 

Fabric is Generally Available. Browse Fabric Presentations. Work towards your Fabric certification with the Cloud Skills Challenge.


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.



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”) ).






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)



  • 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


  • Show on Rows: ON
  • Word Wrap: OFF


  • 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


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!




What is your favorite Power BI feature release for November 2023?