I noticed that when I load my data directly into PBI Online I automatically get YEAR and MONTH columns added next to all DATE columns I have! What is even better is that this MONTH column that PBI Online only adds for me automatically is formatted (January, February, etc...) and the month names actually sort properly - (January, February, and so on). Is this feature coming to PBI Desktop? Or does anyone know how to create a month column the same way so that it shows the MONTH names but most importantly sorts in the correct order (not April, August, and so on). I know how to do this with Excel PowerPivot Tables with the custom sort dialog-box but can't seem to find it in PBI Desktop? I wonder if this is done with DAX or M? Thanks!

We're currently working on this for the desktop .... and it will be a unified experience in both the desktop and service. There are several date time features that will show up. This kind of automatic behavior is among the first that will arrive.

Month Columns in PBI Desktop | Microsoft Fabric Community

43 Replies

Greg_Deckler
Community Champion
11 years ago
Create a new column called "MonthSort" or something and use the formula:

=FORMAT([Date],"mm")

This will give you a two digit number for month.

Select your Month column, Modeling tab and choose "Sort by Column" and set it to MonthSort column.
- Sean
  Community Champion
  11 years ago
  I'm actually trying to sort the Month NAMES (January, February, March, and so on) correctly.
  
  I have a column named Check Date and also a column Sale Date in one of my data sources!
  
  When I load that data in PBI ONLINE NOT in PBI desktop I automatically get Check YEAR and Check MONTH Columns added!
  And also Sale YEAR and Sale MONTH columns added.
  
  So these 4 columns get created for me automatically! (Has anyone else seen this with PBI Online only)
  
  Both the Check MONTH and the Sale MONTH columns that get automatically created => list the months by their names and
  
  Most importantly both of these columns then SORT - January, February, March.... meaning in the correct order!
  
  If I had not seen this in PBI Online I would still be using the numbers for the months.
  
  I'm just wondering if anyone knows how to do this in PBI Desktop using either DAX or M?
  
  If I create a column using => FORMAT(Payments[Check Date], "MMMM") => Month names will sort alphabetically and not in the correct order! (In PowerPivot Tables you could go to a Custom Sort and select Jan, Feb, etc.. Sort)
  - Anonymous
    Not applicable
    11 years ago
    1) What is your data source for the information you are pulling in?
    2) The way you do this is to create a yearmonth calculated column and sort your month on that. This functionality exists in the PBI Desktop and Excel. Highlight the field you want sorted, and select "Sort by Column" and select your numeric column. Then things will sort the way you want.
    This older blog by marcorusso is still relevant to you question.
    
    Are you the owner of the data set? It almost sounds like someone created these sorting fields for you in the dataset...
- apatel
  New Member
  9 years ago
  this does not support in direct query mode
- Rajiv1237
  Resolver I
  9 years ago
  Please give solution for direct query. I don't load data in Power BI.
  I directly generate Power BI report from cube.
ashishrj
Power Participant
11 years ago
Sean You can create a calculted column for your date field as follows:

~MonthNumber=Month([Created Date])

Where MonthNumber is used to extract month number. Then later

~Month=SWITCH([MonthNumber],
1,"January",
2,"Feburary",
3,"March",
4,"April",
5,"May",
6,"June",
7,"July",
8,"August",
9,"September",
10,"October",
11,"November",
12,"December",
"Invalid Month Number"
)
To represents Month in Words

~MonthNo=SWITCH([MonthNumber],
1,"01",
2,"02",
3,"03",
4,"04",
5,"05",
6,"06",
7,"07",
8,"08",
9,"09",
10,"10",
11,"11",
12,"12"
)

~Year=YEAR([Created Date])

~YearMonth=[Year]&[MonthNo]

For custom sorting the month column.
Later to sort, you can follow the following step as shown in image below and also the sorted filed

You can see the output marked in rectangle. Hope this works!
- raprgz
  Microsoft Employee
  10 years ago
  How can you setup this sort for just month? Without the Month & Year?
  - RJ
    Resolver II
    10 years ago
    raprgz
    
    Unsure if this helps or not
    
    http://community.powerbi.com/t5/Desktop/Sorting-by-Month-as-Text-Jan-Feb-Mar-etc/m-p/9244#M1730
    
    This is the way I sorted Jan Feb Mar etc by the month number
    
    I was confused at first so have given a fairly detailed step by step explanation
LanceDelano
Microsoft Employee
11 years ago
We're currently working on this for the desktop .... and it will be a unified experience in both the desktop and service. There are several date time features that will show up. This kind of automatic behavior is among the first that will arrive.
- apamau
  New Member
  11 years ago
  Yes, pls make it easier to auto detect or let us mark and easily inform the system this is month, year, a date or what ?
  thanks and hope that's coming in fast.
  - agraffeo21
    Helper I
    10 years ago
    apamauwas this ever solved?
- Sean
  Community Champion
  11 years ago
  Thanks! That would be great and would save us time when dealing with all date columns in a data model. Looking forward to it!
- starmoonknight
  Helper II
  10 years ago
  Hi Lance,
  
  I just found that if we create a new column that uses FORMAT to customise a Date type column, it is no longer of data type Date but Text.
  
  The thing is that the provided formats for Date could not satisfy all needs, and if we use FORMAT, in the Report View, it'll be sorted alphabetically. It's more than better that we can customise the format but PBI still knows that it is a Date not a Text, so we can make use of the hierarchy and sort it in a meaningful way.
khaled_cs
Frequent Visitor
9 years ago
very simple just make another column
and here is the Expression

Month Name = FORMAT([Your Date Column],"MMM")

wish this help
khaled_cs
Frequent Visitor
9 years ago
very simple just make another column
and here is the Expression

Month Name = FORMAT([Your Date Column],"MMM")

wish this help
wlopezm
Regular Visitor
10 years ago
Although it is possible to do a trick by using DAX formulas and get the desire behavior, there is a more simple solution to get the months ordered correctly. All that you need is the month number and the month name in your data&colon;

Id, Name
1, January
2, Febrary
3, Month
...
12, December

Once in Power BI Desktop, select the month name column and then click on "Sort By Column". Finally, select the Id field so the data gets ordered by Id (1,2,3..12) not by Name (April, August...).
Haider121
Frequent Visitor
9 years ago
Thi is not sorted! we need to be able to sort by chronological month and not sure the number month, if you do shrink the month number down to hide it, it will still show if you pin the visualisation onto a dashboard!
None of the work arounds anywhere work in a proper way.
Haider121
Frequent Visitor
9 years ago
Thi is not sorted! we need to be able to sort by chronological name month and not just the number month, if you do shrink the month number down to hide it on a powerbi table, it will still show up if you pin the visualisation onto a dashboard!
None of the work arounds anywhere on this community site work properly especially if you are using a tabular model to feed your powerbi reports.