Get Local Time on Power BI Service

Question

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?

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