🕓 Handling Timezones in Power BI

How to correctly align UTC data from BigQuery (or other data warehouses) with your local timezone

Why Timezone Issues Happen

Most data warehouses (including Google BigQuery etc.) store timestamps in UTC (Coordinated Universal Time) by default.

This ensures consistency across systems, but can lead to confusion when visualizing data in Power BI, because Power BI assumes timestamps are in your local timezone — unless told otherwise.

Typical symptoms:

Daily or “Yesterday” filters show unexpected results.

Counts or values differ from source systems like HubSpot or Salesforce.

Events appear shifted by several hours (e.g., “10 PM yesterday” appears as “Today”).

Understanding How Power BI Handles Timezones

Layer	Description	Example
Data Source (BigQuery)	Stores all timestamps in UTC.	2025-08-26 00:25:56.957000 UTC
Power Query (M Language)	You can adjust timezone during data load using functions.	DateTimeZone.SwitchZone()
DAX / Model Layer	You can add calculated columns or measures that convert UTC to local time.	DATEADD([Timestamp], 10/24, HOUR)
Visualization	Time intelligence filters (“Yesterday”, “Last 7 days”) depend on your model’s date/time values.	Filter boundaries may shift without conversion.

Best Practice: Keep Data in UTC, Convert at Consumption

Do not change the timezone in BigQuery.Instead, handle timezone conversion in Power BI to keep your data warehouse clean and consistent.

Step-by-Step Example (Convert UTC to AEST in Power BI)

Option 1: Power Query (Recommended for Simplicity)

In Power BI Desktop, open Transform Data → Power Query Editor.

Select your datetime column (e.g., created_at).

Choose Add Column → Custom Column, and enter:

DateTimeZone.SwitchZone(DateTimeZone.From([created_at]), 10)

(For AEST, use +10; for AEDT or daylight saving, use +11.)

Rename this new column to something like created_at_local.

Use this local column in your visuals and filters.

Option 2: DAX Calculated Column

If your data is already loaded, you can create a calculated column:

Created_At_Local = [created_at] + TIME(10,0,0)

This shifts the timestamp by +10 hours (AEST). Adjust accordingly for your region.

Handling Daylight Savings (DST)

Power BI does not automatically adjust for DST.

If your region observes daylight savings (e.g., switching between UTC+10 and UTC+11), you can manage it by:

Creating a mapping table of dates with their corresponding UTC offset, or

Using conditional logic in Power Query:

if Date.Month([created_at]) >= 10 or Date.Month([created_at]) <= 3
then DateTimeZone.SwitchZone(DateTimeZone.From([created_at]), 11)
else DateTimeZone.SwitchZone(DateTimeZone.From([created_at]), 10)