cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Super User
Super User

Month Columns in PBI Desktop

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!

1 ACCEPTED SOLUTION

Accepted Solutions
Power BI Team LanceDelano
Power BI Team

Re: Month Columns in PBI Desktop

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.

Lance
43 REPLIES 43
Super User
Super User

Re: Month Columns in PBI Desktop

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.


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

Proud to be a Datanaut!


Super User
Super User

Re: Month Columns in PBI Desktop

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)

 

 

 

 

 

 

Super User
Super User

Re: Month Columns in PBI Desktop

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

Near SE WI? Join our PUG Milwaukee Brew City PUG
Highlighted
ashishrj Senior Member
Senior Member

Re: Month Columns in PBI Desktop

@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

sort.png

 

You can see the output marked in rectangle. Hope this works!

Super User
Super User

Re: Month Columns in PBI Desktop vs PBI Online

Month and Year Columns get automatically created in PBI Online for all Date Columns???

 

Has anyone else seen this? Hope this feature is coming soon to PBI Desktop.

 

Please test for yourself to see what I mean.

 

 STEP 1 => create a simple Excel file with 1 table called Payments (even with local file works)

Payments table

Company             Check Date         Property              Sale Date             Sale Price

Company 1         1/1/2015              Prop 1   Nov-14 $1.00

Company 2         2/15/2015            Prop 2   Dec-14  $1.00

Company 3         4/1/2015              Prop 3   Jan-15   $1.00

Company 4         5/16/2015            Prop 4   Feb-15  $1.00

Company 5         6/30/2015            Prop 5   Mar-15 $1.00

Company 6         8/14/2015            Prop 6   Apr-15  $1.00

Company 7         9/28/2015            Prop 7   May-15 $1.00

Company 8         11/12/2015         Prop 8   Jun-15   $1.00

Company 9         12/27/2015         Prop 9   Jul-15    $1.00

Company 10       2/10/2016            Prop 10 Aug-15  $1.00

 

STEP 2 => Log in POWER BI Online

STEP 3 => Get Data => Local File => select the sample file

STEP 4 => Explore the new dataset

STEP 5 => Look at the fields => 4 new columns are automatically created => Check Month / Check Year & Sale Month / Sale Year

STEP 6 => Select the newly created by PBI Online Check Month column

NOTE => It sorts the Month NAMES (not numbers) CORRECTLY not alphabetically

 

Follow same procedure in PBI Desktop => those 4 extra columns from STEP 5 do not show up!

 

In my original file PBI Desktop file I have to create those 4 columns and I do also have a YYYY-MM column for other visualizations. But I use numbers for the months. I use Check Year as a slicer and Check Month on the horizotal axis. This lets me draw individual lines for the selected in the slicer year(s) to see seasonal trends. Basically compare 2014 (Jan thru Dec) vs any other year(s) (Jan thru Dec) you select from the slicer. 

 

I stumbled onto this when loading a file directly online!

 

I just want to find out how to create a column in PBI Desktop (with DAX or M) that sorts Month NAMES (not numbers) the same way they sort in PBI Online!

 

Thanks!

 

Super User
Super User

Re: Month Columns in PBI Desktop

Create a calculated column, Month = FORMAT([Check Date],"mmmm"), then create your MonthSort column as above. Set your Month column to have a sort by date of your MonthSort column.

 

If you want year sorting as well (I often just use a slicer for Year), create your MonthSort column by concatenating two calculated columns, year and "MonthSort":

 

Year = FORMAT([Check Date],"yyyy")

 

MonthYearSort = CONCATENATE([Year],[MonthSort])

 

Set your Sort by column to MonthYearSort for the Month column.


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

Proud to be a Datanaut!


Power BI Team LanceDelano
Power BI Team

Re: Month Columns in PBI Desktop

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.

Lance
Super User
Super User

Re: Month Columns in PBI Desktop

Thanks! That would be great and would save us time when dealing with all date columns in a data model. Looking forward to it!

apamau New Member
New Member

Re: Month Columns in PBI Desktop

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.