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]

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

 

[nmss18]

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]

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]

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]

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

 

[buclao]

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/

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]

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]

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

 

[nmss18]

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]

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