change graph X-axis order to fiscal year

[jprheeder]

Hiya,

I've been struggling to get my line graph to order it's x-axis by our fiscal start date (July) rather than calendar month. 

I've followed some answers on this topic for similar questions, to varying degrees of success, ie(https://community.powerbi.com/t5/Desktop/change-graph-X-axis-order-to-fiscal-year/m-p/367746)

as posted in the above link, I have created a conditional column that sets a number based on what the start date should be for a July Start:

= Table.AddColumn(#"Added Custom", "MonthNumber", each if [Month] = 1 then 7 else if [Month] = 2 then 8 else if [Month] = 3 then 9 else if [Month] = 4 then 10 else if [Month] = 5 then 11 else if [Month] = 6 then 12 else if [Month] = 7 then 1 else if [Month] = 8 then 2 else if [Month] = 9 then 3 else if [Month] = 10 then 4 else if [Month] = 11 then 5 else if [Month] = 12 then 6 else null)

Modeling Tab appears to be correct, in that July(7) has been conditionally mapped to MonthNumber(1):

I then flick back to the main Field and on the Date Column, I select the "Sort by column" and sort by MonthNumber

That appears to correctly order the Table, but not the Line graph:

The amounts for January correspond to the relative Fiscal year (ie the following year) but wont show up where they should.

I've tried sorting by Column in both the Modeling tab via selecting the field. 

If I substitute the (Month) Date Hierarchy in the X-Axis with the MonthNumber Field, I get what I'm looking for, but obviously with the drawback of having numbers where the Month Name should be:

Please, teach me Senpai.

[johnyip · Accepted solution]

I think you can create another conditional column in PowerQuery to extract the month name, and then sort that conditional column with "MonthNumber".

Finally, use that conditional column in your line chart.

[jprheeder]

Thanks @johnyip That worked. Looks like it wouldn't sort the Date field, but was happy to sort a text field of the extracted month.

👌