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
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.
Check out the October 2024 Power BI update to learn about new features.
Learn from experts, get hands-on experience, and win awesome prizes.
User | Count |
---|---|
113 | |
89 | |
84 | |
76 | |
64 |
User | Count |
---|---|
135 | |
112 | |
98 | |
98 | |
92 |