Looking for simple DAX formula to spearete date\time

[nmss18]

Hello,
I have cell with a date and time in the following format:
2011-02-01 00:00:00.937
I am trying to figure out how to separate the date and the time from each other in seeparate columns

Any assistance, would be appreciciated.
Thanks,
Nathan

 

[ruve1k]

Please post a link when you cross post.
http://social.msdn.microsoft.com/Fo...l/thread/1b3b490a-c1de-46e7-a282-1d13c35df779

 

[MD610]

It probably depends on the actual format of your date/time field.

If it's text you could simply try the LEFT and RIGHT functions to pull out the desired characters from each part of original field.

If it is formatted as Date/Time then you can probably extract what you need using the FORMAT function in DAX with something like "Short Date" and "Short Time" for the format parameter.

 

[nmss18]

Thanks for replying. How can you determine if the field is in TEXT format or not? In regular excel I would just right click on the cell and click on format cells. That doesnt seem to be the case here.
In deference to the previous post, I also posted the same question here:
http://social.msdn.microsoft.com/Fo...l/thread/1b3b490a-c1de-46e7-a282-1d13c35df779

I am still new to powerpivot so i appreciate the assistance.

Thanks,
Nathan

 

[MD610]

In the PowerPivot window, on the home tab, there is a section on the ribbon called formatting. There is a dropdown called Data Type. This should tell you how the selected column is formatted.

I believe PowerPivot will usually maintain the source formatting.

 

[LindenWolf]

I believe if you ue the Text to Columns you would get the desired results.

Text to columns
Fixed Width

then move selector where desired.

 

[nmss18]

MD160
I must have a different version of PP, because I dont have the Home and Design tabls. I have: File, Edit, View, Table, Column and Help.
I clicked on Column->Formating Type and only Text shows. Samething with Column-> Data Type, there is a checkmark next to 'Text'. When I selected Column->Data Type-> Date, I got an error saying 'Failed to change column data type'.
I tried the LEFT function and was delirious that it worked. I now have the date in one column, but unfortunately, it is still in Text format, and the column->Data Type is greyed out. So, in a pivot table i cant group the dates by month or year.
Any suggestions?

LindenWolf
I couldnt find a 'text to columns' or 'fixed width' in PP. Please advise.


Thanks again,
Nathan

 

[ruve1k]

Use LEFT, MID, RIGHT to capture the part that you want. Then wrap it in the DATEVALUE or TIMEVALUE function to convert the text in the proper data type.

 

[MD610]

I still have the first version of PP, so maybe you have the V2.

I'm still a bit new to PP myself so often times there is a bit of trial and error.

I think between a combination of the FORMAT function and DATEVALUE/TIMEVALUE (as ruve1k mentioned) you can convert the format back and forth to get it correct.

Also, you could try creating a new column for month and a second new column for year and then use the MONTH and YEAR functions respectively to pull out that info into their own columns. I think these functions will work on a date field formatted as date or text. I love having separate month and year fields because then they can be used in the pivot to group the data or in slicers to filter it.

 

[ruve1k]

The FORMAT function is not appropriate in this case. FORMAT converts a number or date into text. Here, we have text and are trying to convert it into a date and time.

Nathan,
You haven't mentioned the method you are using to pull in the data. The easiest way to solve this problem would be to modify the SQL query. If the underlying field in the database is actualy a datetime data type then you might try using

CAST(column as date) as myDate, CAST(column as time) as myTime