Forum Discussion
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.
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.
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)
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...
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!
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.