cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
marcTJP
New Member

Extract Month and Year from DateTime field to get MMM-YY

I am trying to end up with a column that contains the month and year of a DateTime field.

 

I can find how to extract the month from a date or extract the year but I cannot find how to extract the month and year into one field.

2 ACCEPTED SOLUTIONS

Accepted Solutions
Super User IV
Super User IV

Re: Extract Month and Year from DateTime field to get MMM-YY

= MONTH([date] & YEAR([date])

 

Or, the long way:

 

CONCATENATE(MONTH([date]),YEAR([date]))


---------------------------------------

Not the Power BI thought police...

I have NEW book! 
DAX Cookbook from Packt
Over 120 DAX Recipes!
Did I answer your question? Mark my post as a solution!

Proud to be a Datanaut!

View solution in original post

greggyb Resident Rockstar
Resident Rockstar

Re: Extract Month and Year from DateTime field to get MMM-YY

And wrap the whole thing in VALUE() to cast back to a numeric type if you need:

= VALUE( MONTH( [Date] ) & YEAR( [Date] ) )

View solution in original post

18 REPLIES 18
Super User IV
Super User IV

Re: Extract Month and Year from DateTime field to get MMM-YY

= MONTH([date] & YEAR([date])

 

Or, the long way:

 

CONCATENATE(MONTH([date]),YEAR([date]))


---------------------------------------

Not the Power BI thought police...

I have NEW book! 
DAX Cookbook from Packt
Over 120 DAX Recipes!
Did I answer your question? Mark my post as a solution!

Proud to be a Datanaut!

View solution in original post

greggyb Resident Rockstar
Resident Rockstar

Re: Extract Month and Year from DateTime field to get MMM-YY

And wrap the whole thing in VALUE() to cast back to a numeric type if you need:

= VALUE( MONTH( [Date] ) & YEAR( [Date] ) )

View solution in original post

Anonymous
Not applicable

Re: Extract Month and Year from DateTime field to get MMM-YY

This works when you extract Year and Monthnumber like 20141, 20142,20143....201412...but if I want to get 2014 Jan, 2014 Feb, 2014 Mar and so...

 

this forumula works better when you extract monthnumber and yr, Any thoughts on that what I mentioned above.

 

Appreciated your support.

 

Thanks,

kdaubert
Regular Visitor

Re: Extract Month and Year from DateTime field to get MMM-YY

My DateKey looks like this   5/7/2007 12:00:00 AM  and so on.  How do I extract just the month and the year?  I'd like to leave the original column alone and put the new date (month and year) into a new column.  Any help is appreciated.  Thanks!

Karen

Super User IV
Super User IV

Re: Extract Month and Year from DateTime field to get MMM-YY

Hi,

 

Try this

 

=FORMAT(Data[Date],"mmm-yyyy")

 

Hope this helps.


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/
kdaubert
Regular Visitor

Re: Extract Month and Year from DateTime field to get MMM-YY

yes, that worked.  Thank you!

Super User IV
Super User IV

Re: Extract Month and Year from DateTime field to get MMM-YY

Hi,

 

It my reply helped, please mark it as Answer.


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/
nash_wa
New Member

Re: Extract Month and Year from DateTime field to get MMM-YY

I tried doing that but the data got sorted in descending order, meaning, whichever months had higher sales appeared first. I would like to sort the data by month.

vippankapoor
Frequent Visitor

Re: Extract Month and Year from DateTime field to get MMM-YY

Please help if this formula should be used as DAX or M code.

I tried using formula to create custom column (format "MM-YY) from existing date field (Date 2)  in POWER BI desktop however seems it got set up as measure (see attached). Date Capture.JPG

Helpful resources

Announcements
New Ranks Launched March 24th!

New Ranks Launched March 24th!

The time has come: We are finally able to share more details on the brand-new ranks coming to the Power BI Community!

‘Better Together’ Contest Finalists Announced!

‘Better Together’ Contest Finalists Announced!

Congrats to the finalists of our ‘Better Together’-themed T-shirt design contest! Click for the top entries.

Arun 'Triple A' Event Video, Q&A, and Slides

Arun 'Triple A' Event Video, Q&A, and Slides

Missed the Arun 'Triple A' event or want to revisit it? We've got you covered! Check out the video, Q&A, and slides now.

Join THE global Microsoft Power Platform event series.

Join THE global Power Platform event series.

Attend for two days of expert-led learning and innovation on topics like AI and Analytics, powered by Dynamic Communities.

Community Summit North America

Community Summit North America

Innovate, Collaborate, Grow. The top training and networking event across the globe for Microsoft Business Applications

Top Solution Authors
Top Kudoed Authors