cancel
Showing results for 
Search instead for 
Did you mean: 
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.     

image.pngMonths 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

Accepted Solutions
pstueven
Frequent Visitor

Re: Graph with x axis in fiscal year

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

Re: Graph with x axis in fiscal year

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 

Colleen Helper I
Helper I

Re: Graph with x axis in fiscal year

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.

image.pngCannot sort by fisc month intimage.pngData has been sorted by fisc month

pstueven
Frequent Visitor

Re: Graph with x axis in fiscal year

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

Colleen Helper I
Helper I

Re: Graph with x axis in fiscal year

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.

Colleen Helper I
Helper I

Re: Graph with x axis in fiscal year

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

 

pstueven
Frequent Visitor

Re: Graph with x axis in fiscal year

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?

Helpful resources

Announcements
New Ranks Launched March 24th!

New Ranks Launched March 24th!

The time has come: We are finally able to share more details on the brand-new ranks coming to the Power BI Community!

‘Better Together’ Contest Finalists Announced!

‘Better Together’ Contest Finalists Announced!

Congrats to the finalists of our ‘Better Together’-themed T-shirt design contest! Click for the top entries.

Arun 'Triple A' Event Video, Q&A, and Slides

Arun 'Triple A' Event Video, Q&A, and Slides

Missed the Arun 'Triple A' event or want to revisit it? We've got you covered! Check out the video, Q&A, and slides now.

Join THE global Microsoft Power Platform event series.

Join THE global Power Platform event series.

Attend for two days of expert-led learning and innovation on topics like AI and Analytics, powered by Dynamic Communities.

Community Summit North America

Community Summit North America

Innovate, Collaborate, Grow. The top training and networking event across the globe for Microsoft Business Applications

Top Solution Authors
Top Kudoed Authors