How to convert this Text field into a DATE field using DAX formulas?

Question

I am using Excel 2013 and I have a Powerpivot table which pulls data using an SQL query. One of the columns of the output (called MTH) is in TEXT format and shows dates as follows: 2014-09-01 00:00:00.000

I need a new calculated column that would give me the following result, for the example above: Sep-2014 or may be, September 2014

Which DAX formula do I need?

Answer 1

Undoubtedly the best way to deal with this is in your SQL query but you would need to post which flavour of SQL you are using.

Failing that you could try:

= DATE(LEFT([MTH], 4), MID([MTH], 6, 2), MID([MTH], 9, 2))

This will create a 'proper' that you can format as you choose from the drop down menu.