How to create an hourly heatmap in Power BI: step by step

This tutorial shows how to create an hourly heatmap in Power BI to identify activity patterns across days and hours. A heatmap is useful to highlight peaks, idle periods and check daily seasonality in a visual and immediate way.

Prerequisites

• Power BI Desktop installed
• A data source with a timestamp (e.g.: event logs with a DateTime column)
• Basic knowledge of Power Query and DAX

Step 1: Prepare the data in Power Query

Import your table with the DateTime column into Power Query. The goal is to extract the weekday and hour to build the heatmap dimensions.

// No Power Query (Editor Avançado) um exemplo mínimo para adicionar colunas
let
  Fonte = Csv.Document(File.Contents("caminho\meus_dados.csv"),[Delimiter=",", Columns=2, Encoding=1252, QuoteStyle=QuoteStyle.None]),
  Promoted = Table.PromoteHeaders(Fonte, [PromoteAllScalars=true]),
  ChangedType = Table.TransformColumnTypes(Promoted,{{"DateTime","datetime"}}),
  Hora = Table.AddColumn(ChangedType, "Hour", each Date.Hour([DateTime]), Int64.Type),
  DiaSemana = Table.AddColumn(Hora, "Weekday", each Date.DayOfWeekName(Date.From([DateTime]), "pt-PT"))
in
  DiaSemana

Explanation: we created the Hour (0–23) and Weekday (segunda-feira, terça-feira, ...) columns. You can also normalize names or create a numeric order for the days if you want to sort later in the visual.

Step 2: Create ordered weekday table

To ensure correct ordering of days on the heatmap axis (Monday to Sunday), create a small table in DAX with an implicit order.

WeekdayOrder =
DATATABLE(
  "Weekday", STRING,
  "Order", INTEGER,
  {
    {"segunda-feira",1}, {"terça-feira",2}, {"quarta-feira",3},
    {"quinta-feira",4}, {"sexta-feira",5}, {"sábado",6}, {"domingo",7}
  }
)

Then relate WeekdayOrder[Weekday] to the Weekday column of the events table. Set WeekdayOrder[Order] as the sort column for WeekdayOrder[Weekday]. This prevents alphabetical sorting.

Step 3: Create counting and normalization measures

Create DAX measures to count events by day and hour combination. A simple measure and a normalized (percentage) one help the visualization.

ContagemEventos = COUNTROWS('Eventos')

EventosPorCelula =
CALCULATE(
  [ContagemEventos],
  ALLEXCEPT('Eventos', 'Eventos'[Weekday], 'Eventos'[Hour])
)

EventosPercentual =
DIVIDE(
  [EventosPorCelula],
  CALCULATE([ContagemEventos], ALL('Eventos'[Hour])),
  0
)

Explanation: EventosPorCelula aggregates by Weekday+Hour; EventosPercentual normalizes by the total (optional for relative colors).

Step 4: Choose the visual and build the matrix

For a simple heatmap you can use the native Matrix visual with conditional formatting. Another option is a third-party "Heatmap" visual from AppSource. Here we use the Matrix:

1. Insert a Matrix into the report.
2. Rows: WeekdayOrder[Weekday] (already ordered).
3. Columns: Eventos[Hour].
4. Values: EventosPorCelula (or EventosPercentual).

Then apply conditional formatting: under Values choose Background color scales and set the color scale (e.g.: light blue -> dark red). Select minimum, midpoint and maximum or quantiles to highlight average and extreme values.

Step 5: Visual tweaks and interactions

Improve readability with these small adjustments:

• Show rotated column headers to save space (Column headers > Word wrap).
• Set number format with no decimal places for counts.
• Add a date slicer to filter by period (month/range).
• Configure Tooltip to show day total and average per hour using additional measures.

Verify the result

Validate the heatmap by checking: 1) The day ordering is correct (segunda-feira→domingo); 2) Hours appear from 0 to 23; 3) Colors follow the logic of your measures (visible peaks). Test date filters to see if patterns change as expected and compare some totals with a simple table to confirm counts.

Conclusion

You went from raw time-stamped records to an hourly heatmap in Power BI, useful to discover peaks and time-of-day behaviors. Next steps: experiment with different normalizations (per day or per hour), use a Heatmap visual from AppSource for richer layouts, or combine with R/Python for smoothing. Tip: if you see unexpected values, always check time zones and DateTime conversions.