9

As you all might already know, TODAY() function returns UTC time when published to Power BI Service. Our requirement is to return the local (EST) date. As a solution, we created a custom measure that adds UTC offset hours for EST in NOW() and returns the resultant date.

However, this does not handle daylight saving changes as offset changes during these periods.

What are the possible ways to handle this?

Pratik Bhavsar
  • 705
  • 5
  • 26
  • 1
    perhaps this will help: https://radacad.com/solving-dax-time-zone-issue-in-power-bi – RADO Jul 02 '20 at 01:50
  • Is this from a column, single value (like a parameter?), or a measure. Are you restricted to just Dax or is it possible to use M / Power Query functions. – sgoley Aug 25 '20 at 19:49

2 Answers2

1

You can try something like this:

ESTnow=
VAR currentTime = NOW()
VAR timeYear = YEAR(currentTime)

VAR dstStart = DATE(timeYear, 03, 14 - MOD((1+FLOOR(timeYear*5/4,1)),7)) + TIME(2,0,0)
VAR dstEnd = DATE(timeYear, 11, 7 - MOD((1+FLOOR(timeYear*5/4,1)),7)) + TIME(2,0,0)

RETURN IF(currentTime >= dstStart && currentTime <= dstEnd, currentTime - TIME(4,0,0), currentTime - Time(5,0,0))

Daylight savings start on the second Sunday of March and end on the first Sunday of November.

Joao Leal
  • 4,425
  • 1
  • 11
  • 20
-1

A more flexible way to convert UTC date/time for any location (time zone) in the world, and taking Daylight Saving into account, would be to use a Time API service like the one at https://www.timeanddate.com/services/api/time-api.html

LeadingEdger
  • 454
  • 1
  • 7