When working with datasets containing UTC timestamps, especially across multiple U.S. locations, it’s important to convert UTC time to the correct local time based on time zone and daylight saving time (DST) rules.

In this guide, we’ll build a DimDateTime table in Power BI that:

• Contains hourly datetime rows
• Supports UTC to local time conversion
• Accounts for U.S. time zones (Eastern, Central, Mountain, Pacific)
• Automatically adjusts for DST

---

Step 1: Create a Base Date Table

Open Power BI Desktop, go to the Modeling tab, and choose “New Table”.

Paste the following DAX code to create a DimDateTime table from 1990 to 2050 (you can adjust the range):

DimDateTime = 
ADDCOLUMNS(
    CALENDAR(DATE(1990, 1, 1), DATE(2050, 12, 31)),
    "DateTime", [Date] + TIME(0,0,0),
    "Year", YEAR([Date]),
    "Quarter", QUARTER([Date]),
    "YearQuarter", 
        FORMAT([Date], "YYYY") & " Q" & FORMAT(QUARTER([Date]), "0"),
    "Month", MONTH([Date]),
    "Day", DAY([Date]),
    "DayOfWeek", WEEKDAY([Date],2),
    "IsWeekend", IF(WEEKDAY([Date],2) > 5, TRUE(), FALSE())
)

---

Step 2: Add DST Flag Column

• Go to Data view in Power BI
• Select your DimDateTime table
• In the formula bar, add New column using this approach:

We now introduce logic to apply DST rules. First, create a column that determines whether DST is in effect. In the U.S., DST typically starts on the second Sunday in March and ends on the first Sunday in November.

IsDST = 
VAR YearVal = YEAR([Date])
VAR MarchStart = 
    DATE(YearVal, 3, 1) + 
    (14 - WEEKDAY(DATE(YearVal, 3, 1), 1)) + 1
VAR NovEnd = 
    DATE(YearVal, 11, 1) + 
    (7 - WEEKDAY(DATE(YearVal, 11, 1), 1))
RETURN
IF([Date] >= MarchStart && [Date] < NovEnd, TRUE(), FALSE())

---

Step 3: Add UTC Offset Hour Columns

Add additional columns to handle Dynamic UTC offset for major U.S. time zones based on DST:

UTCOffset_Eastern = IF([IsDST], -4, -5)
UTCOffset_Central = IF([IsDST], -5, -6)
UTCOffset_Mountain = IF([IsDST], -6, -7)
UTCOffset_Pacific = IF([IsDST], -7, -8)

Now you have:

• [DateTime] — original UTC datetime
• [IdDST] — whether daylight saving time is observed
• [UTCOffset_Eastern], [UTCOffset_Central]… — numeric offset values to local time with DST support

---

How to Use It

You can now:

• Join this table to your fact table on [Date] and apply the correct UTC offset
• Use it in DAX to calculate local time:

DAXCopyEditLocalTime = 
MAX(FactTable[UTC_Timestamp]) + 
(LOOKUPVALUE(DimDateTime[UTCOffset_Eastern], DimDateTime[Date], MAX(FactTable[UTC_Date])) / 24)

• Dynamically calculate time-shifted metrics without physically converting timestamps upfront

Final Notes

• This table is easy to maintain, since U.S. DST rules are consistent year to year.
• If you need to support non-U.S. time zones, consider building a time zone dimension or using APIs to fetch IANA time zone offsets.
• You can expand this table further with fiscal periods, week numbers, and time zone labels (like EST vs EDT) if needed.