cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
Helper III
Helper III

Seperate Time from Date

I have a [Date] column which includes Date and Time.

I would like to extract the time to have it in a seperate column as well as the date BUT Still keeping the original [Date] column.

I created a copy of the [Date] column and tried to apply a simple format. This worked until I had to use it in an advanced filtering and apparently it still kept the date although it is not shown (because of formatting).

(the table name is MRR_IR_Paper)

 

[Date].PNG

 

 

How it can be done?

 

Thanks!

Nir

 

1 ACCEPTED SOLUTION

Accepted Solutions
Highlighted

Thanks so much!

That helped although I was not able to use it in a visual because it can not accept a measure as Axis.

I think I should go ahead and add another field in the database.

 

Best,

NH

View solution in original post

4 REPLIES 4
Highlighted
Community Support
Community Support

Hi @nhol,

 

You can create a calculated column below: 

Time = FORMAT([Date],"hh:mm:ss")

 

Set the data type of this new column as Time data type: 

 

a1.PNG

 

Best Regards,
Qiuyun Yu

Community Support Team _ Qiuyun Yu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Highlighted

Not sure what I'm doing wrong but PowerBI keeps throwing an error message:

Maybe it is something related to the fact I'm on DirectQuery mode, which is must in my case as I need real time data in my model

 

test.png

 

Any other workaround?

 

Thanks!

Nir

Highlighted

Hi @nhol,

 

As you are using DirectQuery mode, please create a measure rather than calculated column. To make sure Format() function works, please enable below option: 

 

q4.PNG

 

Measure = FORMAT(MAX([CreateTime]),"hh:mm:ss")

 

q5.PNG

 

Or, assume you connect to SQL Server database, you can write T-SQL query to get data: 

 

q6.PNG

 

 

Best Regards,
Qiuyun Yu

Community Support Team _ Qiuyun Yu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Highlighted

Thanks so much!

That helped although I was not able to use it in a visual because it can not accept a measure as Axis.

I think I should go ahead and add another field in the database.

 

Best,

NH

View solution in original post

Helpful resources

Announcements
Community Conference

Power Platform Community Conference

Check out the on demand sessions that are available now!

Community Conference

Microsoft Power Platform Communities

Check out the Winners!

secondImage

Create an end-to-end data and analytics solution

Learn how Power BI works with the latest Azure data and analytics innovations at the digital event with Microsoft CEO Satya Nadella.

Top Solution Authors
Top Kudoed Authors