Starting December 3, join live sessions with database experts and the Microsoft product team to learn just how easy it is to get started
Learn moreGet 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
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!
Solved! Go to 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:
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/
Did I answer your question❓➡️ Please, mark my post as a solution ✔️ |
Also happily accepting Kudos 🙂 |
Feel free to connect with me on LinkedIn! |
#proudtobeasuperuser |
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
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 .
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] ) )
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 =
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 .
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:
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
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! |
#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:
--> duplicate the column by referencing the datetime column:
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/
Did I answer your question❓➡️ Please, mark my post as a solution ✔️ |
Also happily accepting Kudos 🙂 |
Feel free to connect with me on 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?
Did I answer your question❓➡️ Please, mark my post as a solution ✔️ |
Also happily accepting Kudos 🙂 |
Feel free to connect with me on LinkedIn! |
#proudtobeasuperuser |
Thank you @tackytechtom , you solved my problem 🙂 The above IF formula works as well.
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.
Starting December 3, join live sessions with database experts and the Fabric product team to learn just how easy it is to get started.
March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Early Bird pricing ends December 9th.
User | Count |
---|---|
94 | |
92 | |
82 | |
71 | |
49 |
User | Count |
---|---|
143 | |
121 | |
111 | |
59 | |
57 |