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?
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.
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