Forum Discussion

Sean's avatar
Sean
Community Champion
11 years ago
Solved

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 On...
  • LanceDelano's avatar
    LanceDelano
    11 years ago

    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.

teylyn's avatar
teylyn
Advocate III
9 years ago

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's avatar
skocheta
Advocate I
8 years ago

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.