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

Get certified in Microsoft Fabric—for free! For a limited time, get a free DP-600 exam voucher to use by the end of 2024. Register now

Reply
Thankyouverymuc
New Member

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

Thankyouverymuc_0-1654885255631.png

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! 

2 ACCEPTED SOLUTIONS

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:

tomfox_2-1654887005821.png

 

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

tomfox_3-1654887104156.png

NewColumn = TableDateTime[DateTime]
 

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

tomfox_4-1654887222596.png

 

 

The result:

tomfox_5-1654887238150.png

 

 

 

 

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



Did I answer your question➡️ Please, mark my post as a solution ✔️

Also happily accepting Kudos 🙂

Feel free to connect with me on LinkedIn! linkedIn

#proudtobeasuperuser 

View solution in original post

TheoC
Super User
Super User

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

 

 

If I have posted a response that resolves your question, please accept it as a solution to formally close the post.

Also, if you are as passionate about Power BI, DAX and data as I am, please feel free to reach out if you have any questions, queries, or if you simply want to connect and talk to another data geek!

Want to connect?www.linkedin.com/in/theoconias

View solution in original post

22 REPLIES 22
Manishlmn_89
Frequent Visitor

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, 314 - WEEKDAY(DATE(Year, 38), 2))  // Calculates the start of DST (Second Sunday in March).
VAR DST_End = DATE(Year, 117 - WEEKDAY(DATE(Year, 111), 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
Helper I
Helper I

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
Super User
Super User

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

 

 

If I have posted a response that resolves your question, please accept it as a solution to formally close the post.

Also, if you are as passionate about Power BI, DAX and data as I am, please feel free to reach out if you have any questions, queries, or if you simply want to connect and talk to another data geek!

Want to connect?www.linkedin.com/in/theoconias

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

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)

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")

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.

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
 

If I have posted a response that resolves your question, please accept it as a solution to formally close the post.

Also, if you are as passionate about Power BI, DAX and data as I am, please feel free to reach out if you have any questions, queries, or if you simply want to connect and talk to another data geek!

Want to connect?www.linkedin.com/in/theoconias

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?

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

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

@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).  

If I have posted a response that resolves your question, please accept it as a solution to formally close the post.

Also, if you are as passionate about Power BI, DAX and data as I am, please feel free to reach out if you have any questions, queries, or if you simply want to connect and talk to another data geek!

Want to connect?www.linkedin.com/in/theoconias

tackytechtom
Super User
Super User

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/



Did I answer your question➡️ Please, mark my post as a solution ✔️

Also happily accepting Kudos 🙂

Feel free to connect with me on LinkedIn! linkedIn

#proudtobeasuperuser 

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? 

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:

tomfox_2-1654887005821.png

 

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

tomfox_3-1654887104156.png

NewColumn = TableDateTime[DateTime]
 

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

tomfox_4-1654887222596.png

 

 

The result:

tomfox_5-1654887238150.png

 

 

 

 

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



Did I answer your question➡️ Please, mark my post as a solution ✔️

Also happily accepting Kudos 🙂

Feel free to connect with me on LinkedIn! linkedIn

#proudtobeasuperuser 

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

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? 

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


Did I answer your question➡️ Please, mark my post as a solution ✔️

Also happily accepting Kudos 🙂

Feel free to connect with me on LinkedIn! linkedIn

#proudtobeasuperuser 

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

Thankyouverymuc
New Member

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.

Helpful resources

Announcements
November Carousel

Fabric Community Update - November 2024

Find out what's new and trending in the Fabric Community.

Live Sessions with Fabric DB

Be one of the first to start using Fabric Databases

Starting December 3, join live sessions with database experts and the Fabric product team to learn just how easy it is to get started.

Las Vegas 2025

Join us at the Microsoft Fabric Community Conference

March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Early Bird pricing ends December 9th.

Nov PBI Update Carousel

Power BI Monthly Update - November 2024

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