Forum Discussion
Get Time from DateTime field
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_from_a_datetime_columnWe 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/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 Column2. Time = FORMAT ( 'TableName'[DateTimeColumnName] , "hh:mm:ss" )
3. Convert the new Time column from Text to Time.
Hope this helps.
Theo
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/
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_from_a_datetime_columnWe 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/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...
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."