# Looking for simple DAX formula to spearete date\time



## nmss18 (Feb 27, 2012)

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 (Feb 27, 2012)

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


----------



## MD610 (Feb 28, 2012)

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 (Feb 28, 2012)

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 (Feb 28, 2012)

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 (Feb 28, 2012)

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 (Feb 28, 2012)

*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 (Feb 28, 2012)

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 (Feb 28, 2012)

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 (Feb 28, 2012)

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
```


----------



## nmss18 (Feb 27, 2012)

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 (Feb 28, 2012)

You might want to try the VALUE function and see if that converts the text into a proper datetime.


----------



## nmss18 (Feb 28, 2012)

The DateValue function worked, but I still cannot group the individual dates in the pivot table by mionth or year. It seems I will have to further define the Month and year into separate columns.
the SQL admin here created the query into a flat logfil and that is how I inserted it into PP.


----------



## ruve1k (Feb 28, 2012)

Nathan, you should create a Date table and relate the date field in you main table to the date field in your Date table. You should then add Year, Qtr, Month, Week, etc to your Date table not to your main table.


----------



## MD610 (Feb 28, 2012)

I was going to suggest the date table which does give you the most options with displaying and filtering the data by date.

Here is a good link on how to create a date table within PP using the query editor:

http://www.powerpivotpro.com/2011/12/using-query-editor-to-create-a-time-table/


----------



## nmss18 (Feb 28, 2012)

I will try what you both suggested and get back to you later.
I sincerely appreciate the assistance. This has been very helpful.


----------



## buclao (Feb 28, 2012)

MD610 said:


> I was going to suggest the date table which does give you the most options with displaying and filtering the data by date.
> 
> Here is a good link on how to create a date table within PP using the query editor:
> 
> http://www.powerpivotpro.com/2011/12/using-query-editor-to-create-a-time-table/


 


ruve1k said:


> Nathan, you should create a Date table and relate the date field in you main table to the date field in your Date table. You should then add Year, Qtr, Month, Week, etc to your Date table not to your main table.


 
Supporting both answers, you can't go wrong with a homemade dates table!
I would also suggest creating an excel file with a linked table so whenever you get a new file from the sql admin you would just paste that info in that table and hit refresh in the powerpivot window and you'll never have to create a new file or new measures ever! 

In the long run, you MUST speak with your SQL Admin so he can enable you to connect directly to that query instead of getting a flat file.

Hope this helps,

Miguel


----------



## nmss18 (Feb 29, 2012)

I created a date table with columns for the week of, month, quarter and year and linked it to the date column and it worked beautifully! First time, I created a linked table.  I am able to create the pivot tables I am looking for.  I have a few more questions:

1. In the pivot table, when I place MONTH  in the row labes that do not apear in order. I selected the sort option and tried to sort them in order of January, February etc bit it did not work. i see I can sort the fields manually, but I was wondering if I can use a sort option to do this automatically?

2. In my PP table, is there a way I can copy and paste the values of a calculated column to remove an unused column?

Thanks,
Nathan


----------



## ruve1k (Feb 29, 2012)

See the following link http://www.sqlbi.com/articles/sorting-dates-columns-in-powerpivot-v2-and-bism-tabular/
Not sure what you mean. Please explain more clearly what you are trying to accomplish.


----------



## nmss18 (Feb 29, 2012)

Thanks for the article. I'll chjeck up on that shortly. As for my second question, allow me to clarify.
If you recall my first question,I wanted to separate the value of my first column (which is named 'Event Time') which has a date and a time stamp and was in text format. In calculated column1 I used the LEFT function to separate the date from the time. In caluclated column2 I used the DATEVALUE to convert the value in calculated column1 to a date. 
I no longer need the Event Time or Calculated column2 columns, but how can I delete those columns without disrupting calculated column2?
In regular excel, i would copy and paste the values of calculated column2 in the same column and then delete the other columns. What do I do in PP?
thank you.
Nathan


----------



## ruve1k (Feb 29, 2012)

You can't do that. I'm not sure what the big issue is with just leaving the columns where they are but you could try hiding them from client tools so that they don't show up on the PT field list. http://technet.microsoft.com/en-us/library/hh560545(v=sql.110).aspx


----------



## nmss18 (Feb 27, 2012)

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


----------



## nmss18 (Feb 29, 2012)

it isnt a big deal really, just easier on the eyes I suppose. Thanks for all your help, I couldnt have done it (so quickly at least) without all the contributors to this thread.
Sincerely,
Nathan


----------

