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
Anonymous
Not applicable

How to Create a Month Year Column in Calendar

Hello all, 

 

I am trying to find a formular to create a mont year column (eg: November 2019, December 2019) that can also be sorted chronoically (eg: November 2019, December 2019, Janauary 2020, February 2020) from my data calendar. 

 

I have checked out some suggestions here such as 1, 2, 3, 4 but none of them is working for me. I have also tried to find a DAX functions but it only has a month or a year function seperately. 

 

Thank you! 

1 ACCEPTED SOLUTION

@Anonymous ,

Why are giving column = Month Year = format([Date],"mmmm yyyy")

you have to give only

Month Year = format([Date],"mmmm yyyy")

or

Month Year = format([Date];"mmmm yyyy")

Create a month year sort column

Month year Sort = format([Date],"YYYYMM")

View solution in original post

9 REPLIES 9
amitchandak
Super User
Super User

@Anonymous 

Create a column month year

Month Year = format([Date],"mmmm yyyy")

Create a month year sort column

Month year Sort = format([Date],"YYYYMM")

 

Mark this new sort column as sort column of the month year

Refer:https://docs.microsoft.com/en-us/power-bi/desktop-sort-by-column

Sort_by_column.pngSortnewribbion.png

 

Anonymous
Not applicable

Thank you very much! It works perfectly! 

Greg_Deckler
Super User
Super User

Please see this post regarding How to Get Your Question Answered Quickly: https://community.powerbi.com/t5/Community-Blog/How-to-Get-Your-Question-Answered-Quickly/ba-p/38490

 

But, should be something like:

Column = MonthYear = MONTH([Date]) & YEAR([Date])

or

Column = FORMAT([Date],"MMMM YYYY")
 
There are lots of options.

@ 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

My bad. I will check the link out. 

 

It gives me an error. Any idea why?

 

Capture.JPG

Sorry, meant:

MonthYear = 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...
julioms23
Frequent Visitor

Hi send you one example new column en Calendar Table: I don't know if that it's what you need.

 

MONTH L2 = FORMAT('Date'[Date];"mmmm") & " " & 'Date'[Year]

 

Please confirm me.

Anonymous
Not applicable

Hi thank you for your help. Unfortunately, it gives me an error message like this. 

Capture1.JPG

@Anonymous ,

Why are giving column = Month Year = format([Date],"mmmm yyyy")

you have to give only

Month Year = format([Date],"mmmm yyyy")

or

Month Year = format([Date];"mmmm yyyy")

Create a month year sort column

Month year Sort = format([Date],"YYYYMM")

Change ; by ,

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.