Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Colleen
Helper I
Helper I

Graph with x axis in fiscal year

Hello all,

 

Apologies as I understand this is a common problem however the common solution doesn't seem to work! I must be doing something wrong however am not sure what that is.

 

I have a graph displaying sales data per financial year as follows. Our financial year runs from 1-Apr to 31-Mar.     

Months should run Apr to MarMonths should run Apr to Mar

I have created a calculated column for an integer to represent fiscal month from the query editor

 

    #"Added Conditional Column1" = Table.AddColumn(#"Renamed Columns1", "FiscalYear", each if Date.Month([TxDate]) > 3 then Date.Month([TxDate]) -3 else Date.Month([TxDate]) + 9),

Then, sorted by this new conditional column (from both the query editor and the main GUI). Then I proceeded to click on the three dots on top of my visual and sort by 'month'. Note this isn't fiscal month which was the custom column.

 

Why won't the months display as I hope, how can I fix this please?

 

Kind regards,

Colleen

 

(P.S I realise the months are sorted in descending order, I accidentally changed that when applying the sort on the visual, changing it to ascending makes the axis run jan-dec. Unfortunately not Apr-Mar!)

 

EDIT:

Please also note that the first data point in 2015 does not occur in april, instead in June. Added in case this changes anything. I still wish the graph to run Apr-Mar as stated

 

 

1 ACCEPTED SOLUTION

Did you try the button above?

 

sortby-button.JPG

I also had some issues with this. After restarting PowerBI Desktop it worked. Maybe the visual did not update for some reason. If you can't select your column, select another column (like month) on the right side in the fields list and try again. PowerBI's behaviour can be weird sometimes.

View solution in original post

6 REPLIES 6
pstueven
Frequent Visitor

Hey @Colleen ,

Create a new calculated column where you have integers resembling the fiscal month. E.g. 1 for April, 2 for Mai and so on. Then sort your data by this new column.

 

Regards

@pstueven 

Thanks for your reply @pstueven. I believe I have done this and it still doesn't work.

 

See on the data you can see it has been sorted by fiscal month and I see no option on the visual to sort by fiscal month int either.

Cannot sort by fisc month intCannot sort by fisc month intData has been sorted by fisc monthData has been sorted by fisc month

Did you try the button above?

 

sortby-button.JPG

I also had some issues with this. After restarting PowerBI Desktop it worked. Maybe the visual did not update for some reason. If you can't select your column, select another column (like month) on the right side in the fields list and try again. PowerBI's behaviour can be weird sometimes.

I thought it worked - seems that was a fluke. This sort by columns function seems to be very tempermental, and does not seem to act very predictably. I had to restart on the visual and then it wouldn't work. Alas I fear it isn't meant to be

 

Here is what my chart looks like now:

image.png

The integer for which i sort the months on is correct. I am completely lost

 

This is truly weird (but I had similar experiences). Is there still the right column selected in the sort by? If yes, have you tried selecting another column and selecting the right one again in order to trigger a resort by PowerBI?

Thank you so much!

 

I've been trying to fix this for way too long, thank you.

 

For anyone else in the future who encounters this problem and sees this post here's where I went wrong:

 

I was sorting the data from the tabular view in the modelling tab, and in the query editor. That didn't work. Instead you needed to select the field you wanted as your sort from the main report view, click on it, the 'sort by column button' will then not be greyed out so you can click on it. Select the field you wish to sort by, and then on the three dots on the visual sort by this new field.

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.