Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

The ultimate Microsoft Fabric, Power BI, Azure AI & SQL learning event! Join us in Las Vegas from March 26-28, 2024. Use code MSCUST for a $100 discount. Register Now

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
Greg_Deckler
Super User
Super User

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

 

Or, the long way:

 

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


@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
Mastering Power BI 2nd Edition

DAX is easy, CALCULATE makes DAX hard...

View solution in original post

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
kkk333
Frequent Visitor

What if I have issue.PNG

is the case and we need to sort using the month, meaning all the vlaues from each month, if I am not wrong , I need to create a measure for that but could anyone guide me a bit on this , how I will do it  ?

Greg_Deckler
Super User
Super User

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

 

Or, the long way:

 

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


@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
Mastering Power BI 2nd Edition

DAX is easy, CALCULATE makes DAX hard...
Anonymous
Not applicable

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,

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

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

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

Hi,

 

That should be a DAX formula.  Click on the Data Tab in the black bar on the left hand side and then click on Calculated column formula.  Write this formula in the relevant table

 

=FORMAT([Date 2],"mmmm")

 

Hope this helps.


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

Hi Ashish, BIG Thanks for the help. This worked. 

 

One additional challenge i faced is that resultant measure from formula suggested is set up as "Text" field.  I am not able to sort the chart based on calendar months. 

 

Thanks again for guidance and help !!

 

 

Hi,

 

You are welcome.  Please just Google for "Sort months in PowerBI desktop".  Read the answer by mfhussain here - https://community.powerbi.com/t5/Desktop/Sorting-by-Month-as-Text-Jan-Feb-Mar-etc/td-p/9237


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

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

Hi,

 

Try this

 

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

 

Hope this helps.


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

Hi Ashish

 

How can sort by Jan-2019-Dec-2019 instead of date?

 

Please help

Thanks

Sameer

 

Hello Guys,
The process is quite simple.
Create a New Column as:

Modified Date = FORMAT('table name'[date column], "yyyy mm dd")

This will help you in mapping:

Jan 2019 -> 2019 01 01

Feb 2019 -> 2019 02 01

.

.

Dec 2019 -> 2019 12 01

Now build your visualization and use the sort option to sort it using the Modified Date column in ascending
Hope this helps.
Let me know if you need anything else.

 

I have three months: Feb, Mar, Apr but its showing in alphabetical order: Apr, Feb & Mar

 

How shall I resolve this issue?


@Ashish_Mathur wrote:

Hi,

 

Try this

 

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

 

Hope this helps.


 

Hi,

See my post data 6 MArch 2019.  I have shared a link there.


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

This does not work on Direct Query model

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.

yes, that worked.  Thank you!

Hi,

 

It my reply helped, please mark it as Answer.


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

Helpful resources

Announcements
Fabric Community Conference

Microsoft Fabric Community Conference

Join us at our first-ever Microsoft Fabric Community Conference, March 26-28, 2024 in Las Vegas with 100+ sessions by community experts and Microsoft engineering.