Month Columns in PBI Desktop

[Sean]

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!

[khaled_cs]

very simple just make another column 
and here is the Expression 

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

wish this help 

[khaled_cs]

very simple just make another column 
and here is the Expression 

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

wish this help 

[Haider121]

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.

[Haider121]

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.

[wlopezm]

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:

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

[LanceDelano · Accepted solution]

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.

[Haider121]

ITs now 2017 where is this solution? still cannot sort by chronolological test name month?

[teylyn]

You sound upset. It is possible to sort visualisations and tables properly, even if only the month name is being used.

Start in the query and add a custom column called DateSort with this M formula:

=Date.ToText([Date],"yyyyMM")

Replace [Date] with your desired date field. The new column will show the values like in this example. Note how the order is not chronological in the query.

Close and apply the query and go to to data tab and click the Modelling ribbon. Select the Month Name column, click the "Sort by Column" dropdown. Select the DateSort column you just created.

Now you can create data visualisations, charts or tables, where you use just the month name. If you sort by that month name, the result will be chronological, not alphabetical or only by month number. Not in the screenshot how February is at the bottom because it is a date in 2017, whereas all other dates are in 2016. For your benefit, another table shows the full date.

If that is not what you are trying to achieve, maybe you could politely explain your details.

[skocheta]

Hello @teylyn, Thanks for your post. It works absolutely fine for most of the scenarios. However I have a further complexity to this.

I use Live Data sources (SQL as well as Power BI As a Data source), so "Sort by Column" is not an option and is disabled.

I am trying to create a Line chart for Revenue as well as Linear and Fiscal Revenue targets.

This works fine if I have Months number in X Axis , but for obvious reasons, I can't do that.

The Only solution that I could think of is, to create a custom column which has value like 01Jan 02 Feb etc. 

This is not very clever and user friendly 😞

Could you please help if you could think of any alternative.

This is a very basic scenario and I believe many people would have faced this.

[amerriwether]

Teylyn,

Your solution is by far the best. and the only one posted here that works.  Good work.

Anthony

[Haider121]

No I was frustrated earlier but thanks. Your solution is ok for data within powerBI but we run a SSAS tabular model from whichpowerBI reads the data from. In which case the sort by column drop down is greyed out and powerBI has no control of the data.

But its fine I found the option within SSAS properties of the data column where I can change the 'sort by column' and it works. Its just that this whole experience was not intuitive like any of the other BI platforms like qlikview or crystal. You have to look for the  needle in a bunch of needles with powerBI. 

[Anonymous]

Any news on when this will be rolled out?

[EZgrafton]

I don't know if you found a solution for this, but if you didn't just follow these simple steps:

1. create a table with the months / year
2. assign a numerical order to them (1, 2, 3, etc.)
3. link the month / year field on the newly created table to the month / year field on your original table
4. sort the month / year field on your original table by the numerical order you created on 2

It should work. 🙂

[starmoonknight]

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.

[mkorby]

Great. Looking forward to getting this useful Power BI Online feature in Power BI Desktop.

[apamau]

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.

[amysmith]

Hi,  if you wish, you will be able to learn more here:

https://www.udemy.com/power-bi-business-intelligence-with-query-onedrive-and-flow/?couponCode=PB7773...

[teylyn]

@amysmith, you are not attempting to answer the question. The link just leads to a generic landing page in your site. I assume you posted that link to attract traffic. I have reported your post as spam.

[dexterz]

Date =ADDCOLUMNS (

    CALENDAR ( "2010-01-01", "2014-12-31" ),

    "Year", FORMAT ( [Date], "YYYY" ),

    "Quarter", "Q" & FORMAT ( [Date], "Q" ),

    "Month", FORMAT ( [Date], "MM" ),

    "Month", FORMAT ( [Date], "MMM" ),

    "Weekday", FORMAT ( [Date], "ddd" ),

    "WeekdayNum", WEEKDAY ( [Date], 2 ),

    "Day", FORMAT ( [Date], "dd" ),

    "Cal-Month", FORMAT ( [Date], "YYYY-MM" ),

    "Cal-Month", FORMAT ( [Date], "YYYY" ) & "-Q"

        & FORMAT ( [Date], "Q" ))

[agraffeo21]

@apamauwas this ever solved?