cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
marcTJP New Member
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
Super User

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]))


Did I answer your question? Mark my post as a solution!

Proud to be a Datanaut!


greggyb New Contributor
New Contributor

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] ) )
16 REPLIES 16
Super User
Super User

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]))


Did I answer your question? Mark my post as a solution!

Proud to be a Datanaut!


greggyb New Contributor
New Contributor

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] ) )
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 Frequent Visitor
Frequent 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
Super User

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

Hi,

 

Try this

 

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

 

Hope this helps.

kdaubert Frequent Visitor
Frequent Visitor

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

yes, that worked.  Thank you!

Super User
Super User

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

Hi,

 

It my reply helped, please mark it as Answer.

nash_wa Occasional Visitor
Occasional Visitor

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.

Highlighted
vippankapoor Frequent Visitor
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
GregDeckler

How to Get Your Question Answered Quickly

Power BI Super User, Greg Deckler, explains

Summit North America

Power Platform Summit North America

Register by September 5 to save $200

Back to School Contest

Back to School Contest

Engage and empower students with Power BI!

MBAS Gallery

Watch Sessions On Demand!

Continue your learning in our online communities.

Top Ideas
Users Online
Currently online: 32 members 818 guests
Please welcome our newest community members: