Forum Discussion

Thankyouverymuc's avatar
Thankyouverymuc
New Member
4 years ago
Solved

Get Time from DateTime field

Dear Power BI community,    Can anyone please help me with a DAX formula with extracting the time value from a datetime field. See for example the below datetime values and I want to extract the t...
Need Help
Show and Tell
Jon_vB's avatar
Jon_vB
Advocate II
2 years ago

I recently went down a rabbit hole trying to find a way to have Power BI express a time in a different time zone, and I wasn't able to find anything.  I resort to fixing these things in SQL Server (e.g. [myUTCTiemStamp]  At Time Zone 'UTC' At Time Zone 'Central Standard Time').

 

If you always want it converted to AZ time (No daylight savings time), I think you could do

TimeOfDateTime = 

TIME( HOUR(qryAdmissions[DischargeDateTime] - TIME(7,0,0)), MINUTE(qryAdmissions[DischargeDateTime]), 0)

 

If you are willing to store hour offsets, you can do that instead of the 7 that was hardcoded above. But - daylight savings time becomes an annoying barrier.  

 

** would love to hear if anyone has a better solution!  

 

(note that this link has some interesting ideas for Powerquery side. Still a giant hassle if you live somewhere with daylight savings time issues.  https://radacad.com/solving-dax-time-zone-issue-in-power-bi)

Manishlmn_89's avatar
Manishlmn_89
Frequent Visitor
2 years ago

Use this measure to convert UTC to PST or anything.. this is dynamic, based on time , date also will change.. Let utc 12 AM, automatically it will give 8 or 9PM before day .

Current EST_Today =
VAR UTC_DateTime = now() // Variable to hold the original UTC datetime value from your table.
VAR Year = YEAR(UTC_DateTime)         // Extracts the year from the UTC datetime to calculate DST boundaries for that specific year.
VAR DST_Start = DATE(Year, 3, 14 - WEEKDAY(DATE(Year, 3, 8), 2))  // Calculates the start of DST (Second Sunday in March).
VAR DST_End = DATE(Year, 11, 7 - WEEKDAY(DATE(Year, 11, 1), 2))   // Calculates the end of DST (First Sunday in November).
VAR IsDST = IF(UTC_DateTime >= DST_Start && UTC_DateTime < DST_End, -4, -5)  // Determines if the datetime falls in DST period; adjusts offset to -4 for DST (EDT), otherwise -5 for standard time (EST).
VAR LocalDateTime = UTC_DateTime  +IsDST/24
RETURN FORMAT(LocalDateTime, "M/DD/YYYY HH:MM AM/PM")
  • Jon_vB's avatar
    Jon_vB
    Advocate II
    2 years ago

    Thanks!  I definitely like how you added hours vs. how I had it...  +IsDST/24 is straightforward.  AND - the formula I proposed didn't change the date - and your's does.  That's a great point.