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.
Community ChampionCreate 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.
SeanI'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...
- Sean
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!
this does not support in direct query mode
Rajiv1237Resolver I
Please give solution for direct query. I don't load data in Power BI.
I directly generate Power BI report from cube.
Thanks for your answer. It worked for me.
I am just asking myself why it doesn't work when you have 2 digits for a month number column.Thanks in advance.
- teylyn
Advocate III
Why is that not working for me? My column "CreatedDate" is a date column. But when I use the formula
=FORMAT([CreatedDate],"yyyymm")
I get an error "the name 'FORMAT' wasn't recognized. Make sure it's spelled correctly.
- teylyn
OK, found it. The FORMAT function is a DAX function, not an M function. It would be a lot easier if people would point that out when posting a formula.
If you want to create the column in M, then use
=Date.ToText([CreatedDate],"yyyyMM")
With lower case yyyy and uppercase MM.
efglynnAdvocate IV
I wanted to compare months for several years side-by-side in plots, so I created a new variable MMYYYY for sorting purposes:
MMYYYY = FORMAT('Calendar'[calendar_nk],"MM-yyyy")
PowerBI tells me this computed field is a text type, as expected.
After I sort this new column, the dates are not in the text order I'm expecting.
I see:
First entry:
07-2015
Last entry
06-2014
The order of some middle entries doesn't make any sense to me, e.g., ... 12-2016, 04-2017, 06-2017, 01-2017 ...
I'm expecting the order to be something like 01-2014, 01-2015, 01-2016 .... 12-2014, 12-2015, 12-2016
How do I order data for a visualization in MM-YYYY text order?
- efglynn
I tried a numbers only approach and I still couldn't get "sort by column" to work:
MMYYYY = 10000*'Calendar'[month_num] + 'Calendar'[year_num]
Why doesn't "Sort by Columns" give some sort of diagnostic message when it refuses to sort a column?
- teylyn
What happens when you follow the instructions as given in my last post? Did you try that approach? The formula works, the sorting works. The screenshot shows it.
