Adjusting Time Based on DST

It's often a requirement in Power BI reports to use dates or display refresh times. The best practice is to calculate the refresh time using M but if you are using Direct Query or streaming dataset, it's not an option. You have to create a DAX measure. I recently had such requirement to show refresh time based on Pacific Time zone. Below measure calculates the current time in UTC timezone and returns time in Pacific time zone adjusted for daylight savings time. I used official rules from here, which state:

  • DST begins on the second Sunday of March
  • DST ends on the first Sunday of November
Page Refreshed Datetime =
// https://www.nist.gov/pml/time-and-frequency-division/popular-links/daylight-saving-time-dst
// Calculates time in Pacific timezone

VAR _utcnow =
    UTCNOW ()
VAR _year =
    YEAR ( _utcnow )
VAR _utcdate =
    DATE ( YEAR ( _utcnow ), MONTH ( _utcnow ), DAY ( _utcnow ) ) // DST begins on the second Sunday of March    
VAR _dstbegins =
    DATE ( _year, 3, 1 )
        + MOD ( 8 - WEEKDAY ( DATE ( _year, 3, 1 ) ), 7 ) + 7 // DST ends on the first Sunday of November            
VAR _dstends =
    DATE ( _year, 11, 1 )
        + MOD ( 8 - WEEKDAY ( DATE ( _year, 11, 1 ) ), 7 )
VAR _IsDST = _utcdate >= _dstbegins
    && _utcdate < _dstends
RETURN
    SWITCH (
        TRUE,
        _IsDST, _utcnow - TIME ( 7, 00, 00 ), //For Pacific Timezone
        _utcnow - TIME ( 8, 00, 00 )
    )