Get Time from DateTime field

[Thankyouverymuc]

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 timevalue from the datetime field

So for the output, I would like to see 11:17:56 or 3:00:00 in a new column - I want to keep the current column which shows datetime and add a new column with the correct formula. 

Can anyone please help me? 

Thank you guys! 

[tackytechtom · Accepted solution]

Hi @Thankyouverymuc ,

Here a solution for a calculated column as described in this blog post as well:
https://www.tackytech.blog/how-to-crack-the-mystery-of-the-mighty-dax/#22_How_to_retrieve_the_time_f...

We start by duplicating the datetime column:

--> Right click on a column in your table and select new column:

--> duplicate the column by referencing the datetime column:

NewColumn = TableDateTime[DateTime]

Then, just change the format to Time (just like @Syk  suggested in Power Query):

The result:

/Tom
https://www.tackytech.blog/
https://www.instagram.com/tackytechtom/

[Manishlmn_89]

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]

For a pure DAX solution - I came up with the below.  Not sure if it is more or less efficient than making a second copy of the column ... I just don't prefer to do that. 

The dax below would be used to make a Calculated column.  Or - you can use the logic in a measure.

TimeOfDateTime =  TIME(  HOUR(  FactStuff[CreateDateTime]  ),  MINUTE(  FactStuff[CreateDateTime]  ),  SECOND( FactStuff[CreateDateTime]  )  )

[TheoC · Accepted solution]

Sorry to jump in on this forum but noticed there wasn't a DAX solution on here.  If you're looking for a DAX solution, you can extract the Time from a DateTime column by doing the following:

1.  Click on new Calculated Column

2.  Time = FORMAT ( 'TableName'[DateTimeColumnName] , "hh:mm:ss" )

3. Convert the new Time column from Text to Time.

Hope this helps.

Theo

[arothberg]

How would I control what timezeone is used when the datetime is formatted? i.e. the datetime is stored in UTC but I want to format it into a fixed timezone (for example US/Eastern).

[Jon_vB]

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]

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]

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.

[TheoC]

Hi @arothberg, you need to know the difference in hours between UTC and your target timezone. Once you know that, then all you need to do is create a calculated column:

AdjustedTime = FORMAT ( 'Table'[TimeColumn] - TIME ( 0 , 0 , 0 ) , "hh:mm:ss" )

The ( 0 , 0 , 0 ) represents Hours , Minutes , Seconds.  So if you want to take 5 hours off your TimeColumn, then just write ( 5 , 0 , 0 ) in the above formula.  Make sure to adjust the - / + according to the timezone you're after.

Hope this helps.

Theo

[arothberg]

How would I handle a timezone which has a variable offset to UTC (aka day lights savings)? Is there no function that can be used for a calculated column that hanldes proper timezone conversions?

[Jon_vB]

Here's the idea that could use votes and maybe we get this some day. I just voted.  Microsoft Idea

[Jon_vB]

Here's the idea that could use votes and maybe we get this some day. I just did.  Microsoft Idea

[TheoC]

@arothberg easiest way is just creating a DateTimeZones table.  You can get historical and future TimeZone data freely available and create your own table.  From here, just perform a LOOKUP from your Table[DateTime] to the DateTimeZones table with the logic [DateTime] is >= 'DateTimeZones'[DateTimeStart] && <= 'DateTimeZones'[DateTimeEnd]. 

The other way is checking out how @mahoneypat has documented it here: https://community.fabric.microsoft.com/t5/Power-Query/Converting-Time-Zones/td-p/2321771

In terms of your question, in the front end using DAX, not really (not at this stage that I am aware of).  

[tackytechtom]

Hi @Thankyouverymuc ,

For a solution in DAX, create a calculated column where you duplicate the datetime column. Then, just change the format to Time (just like @Syk  suggested in Power Query).

Let me know if this helps 🙂

/Tom
https://www.tackytech.blog/
https://www.instagram.com/tackytechtom/

[Thankyouverymuc]

Hi Tomfox, 

First of all, thank you so much for your quick response. 

As I stated here below, i can't do that solution because I can't acces the table through Power Query due to rights that I currently have on that table (this is getting fixed) and that's why I was asking whether this is possible through a DAX formula. Do you have an alternative solution? 

[tackytechtom · Accepted solution]

Hi @Thankyouverymuc ,

Here a solution for a calculated column as described in this blog post as well:
https://www.tackytech.blog/how-to-crack-the-mystery-of-the-mighty-dax/#22_How_to_retrieve_the_time_f...

We start by duplicating the datetime column:

--> Right click on a column in your table and select new column:

--> duplicate the column by referencing the datetime column:

NewColumn = TableDateTime[DateTime]

Then, just change the format to Time (just like @Syk  suggested in Power Query):

The result:

/Tom
https://www.tackytech.blog/
https://www.instagram.com/tackytechtom/

[Clinical_Epi]

What solution do you suggest if working in Direct Query - as using query editor to create a new column, or custom column shows this message - which wont let one past it:

"This query contains transformations that can't be used for DirectQuery."

[Thankyouverymuc]

Thank you sir! this works.

However, when I try to make a formula that says IF(TableDateTime[NewColumn]=time(03,00,00),1,2) it never shows 1 even though the cell shows that it's 3:00:00.  Do you know how I can fix this? 

[tackytechtom]

Hi @Thankyouverymuc ,

Good question and I do not know the exact answer. Maybe it's because the TIME function returns datetime and PBI cannot compare DateTime with Time.

Maybe try this?

Column 2 = IF ( HOUR([NewColumn]) = 3 && MINUTE([NewColumn]) = 0 && SECOND([NewColumn]) = 0, 1, 2)

Please, do not forget to mark the answers as solutions that solve your issue 🙂 Other readers will have it easier to find their way through if they happen to get to this thread here...

/Tom
https://www.tackytech.blog/
https://www.instagram.com/tackytechtom/

[Thankyouverymuc]

Thank you @tackytechtom , you solved my problem 🙂 The above IF formula works as well. 

[Thankyouverymuc]

Hi Guys! Thank you both for your quick responses but I can't work through the Power Query because I don't have the rights yet so could you please give me a solution with a DAX formula? In Excel, there are various ways through which I can convert it but somehow I can't find it with DAX.