Forum Discussion
Community ChampionMonth 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.
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...
Community ChampionMonth 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!
- Greg_Deckler
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.
EZgraftonAdvocate II
Hello,
I have a similar issue. I did everything you suggested but the columns on my visual are still looking like below. I need May 2015 to be the first column and then the other 12 rolling months. Please help!
