How to order calendar months for data of different financial year periods

[KeepCalm007]

Hello Power BI community

I am trying to developing a visual to show last year (F17) and this year's (F18) data on a line graph. I am stuck on how to sort my axis from July to June. What it currently looks like:

What I want it to look like:

I have had a look around to forum to find that suggestions to solve similar problems involve making a 'sorting column', however I'm quite confused about how to make July, regardless of Year = 1. Can anyone offer some advice on how best to make this work? A sample of my data below. Any suggestions appreciated!

[v-qiuyu-msft · Accepted solution]

Hi @KeepCalm007,

You can create a calculated column below: 

FiscalMonth = SWITCH(MONTH('Table1'[Month]),7,1,8,2,9,3,10,4,11,5,12,6,1,7,2,8,3,9,4,10,5,11,6,12)

Then place this column to the chart Tooltips section, and set as MAX aggregation. Go to the chart, set it's sort by this column. For details, you can download attached pbix file. 

Best Regards,
Qiuyun Yu 

[quyenduong]

Thank you @v-qiuyu-msft  for your solution, it works for me. However, the Max of Fiscal Month always appear in the chart title automatically, do you have any solution for this name doesnt appear in the chart tittle? 
Thank you for your answer 

[KeepCalm007]

Thank you both @GilbertQ and @v-qiuyu-msft for your ideas. 

I used your solution @v-qiuyu-msft and it worked,eventually!

- For a while it wasn't working, I think there may be a bug with my 'Sort by', as it wouldn't do Ascending sort order correctly for a while, I almost gave up! 

Thank you very much for the tips

[GilbertQ]

Hi @KeepCalm007

What I can suggest doing is to create a Date table. In this Date table you can have Calendar Month's and Fiscal Months.

Then what you can have is the Fiscal Month sorted by the Fiscal Start month, which will give you what you are looking for.

Then instead of using the Calendar Month in your visual you just use the Fiscal Month.

You can find my blog post here explaining on how to create your Date table, which once configured will already have the Fiscal Month Sort Order done for you: https://www.fourmoo.com/2016/09/13/power-bi-how-to-easily-create-dynamic-date-tabledimension-with-fi...