Visualizing an Entire Year with a Power BI Calendar Heatmap Using Matrix

A heatmap is a data visualization technique that uses color intensity or gradients to represent the size of values. Instead of showing raw numbers, it highlights patterns, trends, and outliers by mapping data to colors.

 

Power BI doesn’t have a built-in heatmap visual. In many cases, you can get by with a matrix visual plus background conditional formatting, but it becomes tricky when you need a full calendar-style heatmap. This guide walks through how to build one.

 

Step 1: Build a Calendar Table

Start with a proper calendar table containing a continuous set of dates. Make sure it has a one-to-many relationship with your fact table. Add these extra columns that usually aren’t in a standard calendar table:

• Month in Quarter  -  e.g., 1 for January, 2 for February, 3 for March.
• Week of Month  -  Week 1 runs from the first day of the month until the end of that week. For example, if the 1st is a Thursday and weeks start on Sunday, then Week 1 is Thursday–Saturday, and Week 2 starts on Sunday.
• Invisible columns  -  Columns filled with zero-width spaces. These will later replace the quarter, week of month, and month-in-quarter values with invisible ones so they don’t clutter the visual.

 

Sample calculated columns include:

Month in Quarter DAX = 
IF (
    MOD ( DatesTable[Month Number], 3 ) = 0,
    3,
    MOD ( DatesTable[Month Number], 3 )
)

Week of Month = 
VAR _StartOfMonth =
    EOMONTH ( DatesTable[Date], -1 ) + 1
VAR DayOffset =
    WEEKDAY ( _StartOfMonth, 1 ) - 1
RETURN
    INT ( ( DAY ( DatesTable[Date] ) + DayOffset - 1 ) / 7 ) + 1


Invisible Quarter DAX = 
REPT ( UNICHAR ( 8203 ), DatesTable[Quarter Number] )

Invisible Month in Quarter DAX = 
REPT ( UNICHAR ( 8203 ), DatesTable[Month in Quarter] )

Step 2: Create a Week Number Table

Build a small Week Number table with values 0 to 6.

• Week 0 is reserved for the month name (in MMM format so it fits neatly).
• Actual days will only plot in weeks 1 to 6.
• Add an Invisible Week Number column using the same trick as the invisible month column.

Sample Week Number calculated table

Week Number = 
SELECTCOLUMNS (
    GENERATESERIES ( 0, 6, 1 ),
    "Week Number", [Value],
    "Invisible Week Number", REPT ( UNICHAR ( 8203 ), [Value] )
)

Step 3: Set Up the Matrix Visual

1. Create a simple Dummy measure = 1.
2. Add a Matrix visual:
• Format style = None
• Layout = Compact
• Disable row totals
• Disable column subtotal for Month in Quarter, but keep it for Day Name
• Change the subtotal label to a blank space

At this stage, the matrix will be structured but still waiting values.

 

Step 4: Display Month Names

Update the Dummy measure to show month names only in the right place (week 0, middle of the week). Example:

Calendar Value =
VAR _MonthName =
    SELECTEDVALUE ( DatesTable[Name of Month Short] )
RETURN
    SWITCH (
        TRUE (),
        SELECTEDVALUE ( DatesTable[Day Name Short] ) = "Wed"
            && SELECTEDVALUE ( Weeks[Week Number] ) = 0, _MonthName,
        SELECTEDVALUE ( Weeks[Week Number] ) = 0, "",
        "1"
    )

Now, the matrix shows month names at the correct position.

Step 5: Plot Days of the Month

Replace the measure with logic to display the day numbers for weeks 1–6, leaving  empty strings for column subtotals. Example:

Calendar Value = 
VAR _MonthName =
    SELECTEDVALUE ( DatesTable[Name of Month Short] )
VAR _day =
    FORMAT (
        CALCULATE (
            SELECTEDVALUE ( DatesTable[Day] ),
            DatesTable[Week of Month] = SELECTEDVALUE ( Weeks[Week Number] )
        ),
        "0"
    )
VAR _DayCountWeek =
    COUNTROWS (
        INTERSECT (
            VALUES ( Weeks[Week Number] ),
            VALUES ( DatesTable[Week of Month] )
        )
    )
RETURN
    SWITCH (
        TRUE (),
        SELECTEDVALUE ( DatesTable[Day Name Short] ) = "Wed"
            && SELECTEDVALUE ( Weeks[Week Number] ) = 0, _MonthName,
        ISBLANK ( _DayCountWeek ), BLANK (),
        SELECTEDVALUE ( Weeks[Week Number] ) = 0
            || NOT ( HASONEVALUE ( DatesTable[Day Name Short] ) ), "",
        _day
    )

The _DayCountWeek variable ensures week 6 is hidden when there are no days, keeping spacing consistent.

 

Because the visual isn’t filtered to a single year yet, the matrix will look cluttered and disorganized.

 

Filter the visual to a single year to clean it up.

 

 

Step 6: Clean Up the Visual

• Replace the quarter, week, and month-in-quarter columns with the invisible-character versions.
• Rename headers to blank spaces.
• Turn off expand/collapse icons.
• Remove gridlines except for the top of the values section.

 

 

Step 7: Add the Heatmap Colors

Finally, create a measure for conditional formatting:

Heatmap Value = 
CALCULATE (
    [Sales Amt],
    DatesTable[Week of Month] = SELECTEDVALUE ( 'Weeks'[Week Number] )
)

Apply this to background color formatting for values only. Do not format blanks as zeroes.

Color scheme example:

• Highest = #4CAF50
• Lowest = #F0FBF0

Choose colors that keep text readable, even with darker shades.

 

Want to watch a video tutorial instead? Check out this vlog - https://youtu.be/buk87dES558