How to make a calendar heatmap in Power BI
A calendar heatmap shows one value per day in a grid of weeks, so patterns such as weekends, seasons and gaps stand out at once. Power BI has no built-in version, but you can build one with a matrix or a custom visual. Either way, the data model matters more than the chart.
Why this is harder than it looks
People drop a date and a measure into a visual and get a bar chart, a line or a scrambled matrix. Missing days vanish, zero looks like nothing, fiscal years do not start in January, and week columns mix across years. Without a proper date table and clear color rules, the picture misleads.
Step by step
- Build a date table. Create a calendar table covering every day, for example with the DAX function CALENDARAUTO(), or Date = CALENDAR(DATE(2024,1,1), DATE(2026,12,31)). Then choose Table tools > Mark as date table. Relate its Date column to your fact table. Every day must exist, or blank days will be missing from the grid.
- Add calendar helper columns. Add columns for Year, Month number, Weekday = WEEKDAY([Date],2) so Monday is 1, and a week index such as WEEKNUM([Date],2). For fiscal years, shift the month: Fiscal Year = YEAR(EDATE([Date], 12 - startMonth + 1))-like logic, tested against your own fiscal start month.
- Choose a visual. The quick route is a Matrix: Weekday on rows, week number on columns, the measure in values. Alternatively, open the Visualizations pane, choose Get more visuals and look for a calendar heatmap visual. Custom visuals handle layout, year stacking and legends for you, which a matrix cannot.
- Color the cells. In a matrix, use Format > Cell elements > Background color, choose Gradient and set minimum and maximum colors. Use a single-hue sequential palette, light for low and dark for high, because it stays readable in grayscale and for color blindness. Avoid red-green scales.
- Treat zero, blank and future days differently. A zero sale and a missing record mean different things, so do not merge them. In DAX, return BLANK() when no row exists and 0 only when a row has a zero. Give blanks a distinct look, such as a hatch or gray, and shade future days faintly so empty is not read as bad.
- Use one shared scale across years. If you stack years, apply the same minimum and maximum colors so years can be compared. A separate scale per year hides growth. Cap the top at a high percentile such as the 95th, so one outlier day does not turn every other cell pale.
- Add tooltips and test the totals. Add the date, the value and one comparison measure to tooltips. Then compare the heatmap total with a card visual. Check one fiscal-year boundary, a leap day and a duplicated date to confirm that aggregation, sum or average, is the one you intended.
Common mistakes
- Using a fact table dates without a complete date table
- Showing zero and missing days in the same color
- Giving each year its own color scale
- Using red and green as the only color contrast
Where Calendar Heatmap visual for Power BI fits in
Calendar Heatmap is a Power BI custom visual for this task: bind one Date and one Value, with optional tooltip measures, and choose how duplicate dates aggregate, with sum by default. It shows fiscal years and months, marks missing, blank and future days differently, and reports coverage and streak statistics. It is built as a .pbiviz package.
Calendar Heatmap visual for Power BI support and FAQ
FAQ
No. You can approximate it with a matrix and conditional background colors, or use a custom visual from the visuals store or a packaged file. Custom visuals handle year layout and legends more easily.
Days with no rows in the fact table do not appear unless a date table supplies them. Build a full date table, relate it to your data, and show missing days with a separate style.
Add a Fiscal Year column to the date table by shifting months by your fiscal start month, and use it instead of the calendar year for stacking. Test the first and last day of a fiscal year.