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, the Microsoft Fabric Community team will be offering free DP-600 exam vouchers. Prepare 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
OCT PBI Update Carousel

Power BI Monthly Update - October 2024

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

September Hackathon Carousel

Microsoft Fabric & AI Learning Hackathon

Learn from experts, get hands-on experience, and win awesome prizes.

October NL Carousel

Fabric Community Update - October 2024

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