Re-ordering Months on a Graph

[5thGreen]

I’m looking for assistance in re-ordering on the months on the X axis of a graph please.

My financial year runs from April - March and I’ve got actual data from April - October with budget data from April - March. When I display the data on a standard bar chart by month comparing actuals to budget, the graph shows Jan - Mar as the left most months on the X axis when I want them showing as the right most i.e. appearing after December.

I’ve used the Date table that is provided in the beginner learning series and have redone my FY quarters as per the YouTube clip on that topic. I’ve tried creating caluated columns and using Power Query on the date table to get the graph to display correctly but I appear to be failing miserably!

Any help would be hugely appreciated.

[BrianJ]

@5thGreen,

Welcome to the forum! I think you’ll find this to be a great resource and a really helpful community.

I think the solution here is just to sort your x-axis by the fiscal year column in your date table (also be sure that your date table is marked as a date table, so that the time intelligence functions work properly). Here are a couple of forum posts that address similar questions.

Short Month Column not sorting in Slicer

@AoakeP, If you sort Short Month by the MonthOfYear field (see screenshot below) it will put your slicer in correct order. MonthOfYear is just the month number without the year, which is what you need to avoid the error message you were getting. -Brian [image]

Filter Order in Visual

@robH, I’ve web scraped a simple example akin to your situation. In my example below, the only sort options in the visual are Player and Salary. But, suppose I want to sort the graph by Player Age? [image] Using the technique above, define the sort order for Player using Age: [image] Now, when I sort the original graphic by Player, instead of sorting alphabetically as is the default, it will sort by Age: [image] Hope this is helpful. Brian

If these don’t solve your problem, please post your PBIX file so that I can provide a more specific solution.

Thanks.

• Brian

[5thGreen]

Hi Brian,

Thanks for your reply. I only have FY20 data in the model so I don’t believe that is an issue. I have done the sort just to be certain though and nothing has changed.

I suspect this will be a simple fix and I’m doing something incredibly stupid … although as they say, there’s no such thing as a stupid question!

File attached as requested.Fallproof High Level.pbix (190.3 KB)

All the best
Paul

[Melissa]

Hi Paul,

I saw this article recently and I think you will find it helpful.

Sorting months in fiscal calendars - SQLBI

[BrianJ]

@5thGreen,

No, definitely not stupid at all. It required creation of a new column (FYQinMonth) in your Date table to properly sort the X axis column (FYMonthOnly) by.

FYQinMonth = LEFT( [MonthnYear], 2 ) & RIGHT( ‘Date Table’[FY], 2) &RIGHT([FY Quarters], 1) & RIGHT( ‘Date Table’[MonthnYear], 4 )

FYMonthOnly = ‘Date Table’[MonthName]

You should be good to go now. Full solution file posted below.

• Brian
  eDNA Forum - Fallproof High Level - Solution.pbix (190.7 KB)

[5thGreen]

@BrianJ that is awesome! Thank you so much. I’m incredibly grateful and owe you a beer!

@Melissa thanks for that article too. This also looks to be really useful. I’ll read it throughly a little later on in my afternoon.

[5thGreen]

Hi again @BrianJ

I’ve started building another model and entered the dates including your additional columns referred to above. I’ve spent the last 2 hrs pulling my hair out as I’m certain I’ve done it correctly by following all the steps and yet when I quickly tested the data, the date order sorts as per the screen shot I’ve attached.

What am I doing wrong? I’m really keen to get this nailed as it’s important to me to understand every step so I continue to learn throughout the entire process.

I’ve attached the pbix file in the hope you can spot what I cannot.

Many thanks Fallproof Detailed Report.pbix (315.2 KB)

[BrianJ]

@5thGreen,

The trick with sorting columns is finding the right “sort by” column. In this case, you want to sort FYMonthOnly by a column that numbers the months sequentially - in this case it’s the MonthOfYear column. If you go to the Data view, click on FYMonthOnly and sort by MonthOfYear, you’ll be good to go:

Result:

@sam.mckay’s data table code is an amazing resource - I use it in every one of my reports, but the field names are hard for me to keep straight. Thus, I created the following cheat sheet, which I keep on my desktop to help me figure out the correct columns to sort and sort by. You may find it useful for this purpose as well.

I hope this is helpful to you. Give a shout if you have any other problems.

• Brian

[5thGreen]

Thanks @BrianJ this is exactly what I needed. I had been randomly trying to sort the columns but was getting error messages. Your explanation makes perfect sense and my issue seems to now be corrected!

Appreciate the help!

[BrianJ]

@5thGreen,

Great – glad to help. I (and I suspect almost everyone here) know all too well the feeling of “WHY WON’T THIS WORK? THIS SHOULD WORK!”.

Sometimes it just helps to get a second pair of eyes on the problem and a different perspective, which is why this forum is such a great resource. No matter how stuck you get, there’s always someone out there with a creative approach to get you back on track.

• Brian