Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.

Reply
mll92
Frequent Visitor

Measure to Convert UTC to local time, accounting for daylight savings

Hello. I am creating a dashboard using a direct query to Dataverse, with automatic page refresh when a change is detected. Because I am using direct query, I can do very little transformation in Power Query. And because I am using automatic page refreshes, I cannot use calculated tables or columns because then the visuals do not refresh when a change is detected.

 

As a result, most of my table columns in the table visuals are coming from measures. I have one measure to convert the time from UTC to local (local date = Selectedvalue(table[date/time]) - TIME(5,0,0). 

 

However, I need a way to condition this statement so if the date falls within daylight savings time, the measure will only subtract 4 hours. Is this possible? I found several articles on how to do this in power query, but I cannot use power query for this.

 

1 ACCEPTED SOLUTION

hi @mll92 

tried to write a measure like this:

Measure = 
VAR _table = 
ADDCOLUMNS(
    ALL(DateTable[Date]),
    "Day",
    WEEKDAY(DateTable[Date], 2),
    "Month",
    MONTH(DateTable[Date])
)
VAR _table1 = 
FILTER(
    _table,
    [Month]=3&&[Day] = 7
)
VAR _shiftdate = 
EXCEPT(
    TOPN(
      2,
      _table1,
      DateTable[Date],
      ASC
    ),
TOPN(
      1,
      _table1,
      DateTable[Date],
      ASC
    )
)
VAR _shiftdate2 = MINX(_shiftdate, [Date])
RETURN
IF(
    MAX([Date])<_shiftdate2,
    MAX([Date]) - TIME(5,0,0),
    MAX([Date]) - TIME(4,0,0)
)

When plot a table visual with the date column, it worked like this:

FreemanZ_0-1674569959255.png

 

View solution in original post

4 REPLIES 4
mll92
Frequent Visitor

On the second Sunday in March, the difference between UCT and local will change from -5 hours to -4 hours. Then return to -5 on the first Sunday in November

hi @mll92 

tried to write a measure like this:

Measure = 
VAR _table = 
ADDCOLUMNS(
    ALL(DateTable[Date]),
    "Day",
    WEEKDAY(DateTable[Date], 2),
    "Month",
    MONTH(DateTable[Date])
)
VAR _table1 = 
FILTER(
    _table,
    [Month]=3&&[Day] = 7
)
VAR _shiftdate = 
EXCEPT(
    TOPN(
      2,
      _table1,
      DateTable[Date],
      ASC
    ),
TOPN(
      1,
      _table1,
      DateTable[Date],
      ASC
    )
)
VAR _shiftdate2 = MINX(_shiftdate, [Date])
RETURN
IF(
    MAX([Date])<_shiftdate2,
    MAX([Date]) - TIME(5,0,0),
    MAX([Date]) - TIME(4,0,0)
)

When plot a table visual with the date column, it worked like this:

FreemanZ_0-1674569959255.png

 

Thank you!

FreemanZ
Super User
Super User

hi @mll92 

how is daylight savings time is defined in your region?

Helpful resources

Announcements
RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.

MayPowerBICarousel

Power BI Monthly Update - May 2024

Check out the May 2024 Power BI update to learn about new features.