Months not in order (School Year)

[bksbGeorge]

Hi,

I am very new to Power BI and have only started using this in the last 2 days. The organisation I am employed by work around the UK school calendar. This means it runs from the 1st August until 31st July. Any information that is imported into Power BI reflects this but the system reorders the months either chronologically or alphabetically.

Is there any to amend this so it reflects how my organisation works?

Thank you in advance,

George

[AlanTyler]

Hey. The British calendar is the calendar on which most companies work. I really enjoy working on the British calendar. Once I wrote a scientific piece, the theme of which was the British calendar of great schools. But it was important to me that there were no mistakes in my work, so I checked them with the help of - https://ca.papersowl.com, I advise everyone to work on the British calendar, then we will all have weekends on the same days.

[Anonymous]

Hi George -

Welcome to Power BI!

There's a pretty easy fix to this problem.  First, make sure you have a column in your dataset that says August is the 1st month of the year, September is the 2nd, and July is the 12th.  I typically call this column "MonthSort".  You can build this in Query Editor in a number of ways, for example, add this as a custom column:

= if Date.Month([ Your Date Column ]) >= 8 
then Date.Month([ Your Date Column ]) - 7 /* shift August-December to months 1-5 */
else Date.Month([ Your Date Column ]) + 5 /* shift January-July to months 5-12 */

Then, highlight the Month Name column in the main Power BI window.  Click on the Modeling button on the ribbon, and then click "Sort by Column".  Then choose your [MonthSort] column.

Now your column will be sorted in the 1-12 order that you specified, with August first and July last.

Let us know if you have any other issues!

[bksbGeorge]

Hi Chris

Thank you for the response, it was structued really well. I have tried this method you have provided and it doesn't seem to be working. I have created the custom column and query which all seems to be fine. I then highlight Date and sort by column but nothing changes.

Any help would be apprecated?

Thanks

George

[Anonymous]

@bksbGeorge sort your MONTH column by this new custom column.

[bksbGeorge]

Hi Chirs

Once I have set up the query that has no errors it does assign the correct numbers e.g. August is 1st, September 2nd and so on. I then go back to the main screen and sort the Month column by MonthSort but nothing changes, it keeps the normal calendar order. Please see images below.

Thanks

[DeborahPerreaul]

Lynchburg climbed back on top with a [url=https://url4.org/w2axp]grademiners[/url] three-run third. Pantoja led off the frame with a single and Clement walked, putting two on with nobody out. Longo blasted an RBI double to score Pantoja, and Trenton Brooks followed with a two-run double put the Hillcats ahead 4-3.

[Stachu]

the last missing piece is changing the ordering to the calculated column you created

[bksbGeorge]

I have done this but when I go to create a graph it doens't keep the same order, please see below.

[Anonymous · Accepted solution]

Ahhh, you have that month field formatted as a date, and the auto date hierarchy is still turned on.  Here's how to fix that.

1) Delete the current visual.

2) Click File ->Options and Settings -> Options

3 Under Current File (on the left side, click Data Load

4) Under Time Intelligence, remove the check for Auto Date/Time

5) Rebuild your visual

The Auto Date/Time actually creates a hidden date table with columns for Year, Quarter, Month, and Date.  However, this table will not respect the sort order that you specified, so it's putting January first.

You can see evidence of this in the 2nd picture you posted.  Look at the Visualizations pane.  Under Values, you see:

x Month

   x Month

x MonthSort

That extra month is the evidence that it's using the hierarchy for the date that it found in your data model.  Even though you're calling that column Month, it's actually a formatted as a date, as there is a Day, Month, and Year component.  

Had your Month column simply been January, February, March, etc. as text, this issue wouldn't have come up.  On the bright side, you get 2 lessons in 1!

I typically disable this Auto Date/Time function on all new files I create, as best practice is to create you own calendar table.  DAX has some wonderful time intelligence functions that really shine when a calendar table is used.

[bksbGeorge]

Thanks for your help with this, it has been very useful. I feel like I've been given a lot of great knowledge to take away and can begin applying it within power BI.

Thanks

George