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
BVdL
Regular Visitor

change graph X-axis order to fiscal year

Hello all,

 

I'm currently working with an extensive dataset to generate a number of charts and graphs based on dates. However, we would like to represent these in a fiscal year order (i.e. July to June, rather than January to december). Since January, we are encountering the issue below, where data for january and february are listed before july of the year before, and we do not succeed in changing this. 

I haven't been able to reformat the dates in the table to something that would sort on year rather than over month except for drilling down to 'year', but that would mean we cannot view monthly evolutions.

 

Is there any way I can change the internal calendar calculation of Power Bi, or do I have to create a new measure/column and change all graphs? (in the second case: any hints on a successful formula? I have tried some that I found on the internet, but without succes. My dataset represents dates as dd/mm/yyyy, which is automatically changed by Power Bi into eg. "Friday, 22 december 2017"

Capture.PNG

 Thanks in advance!

 

Boris

1 ACCEPTED SOLUTION
rocky09
Solution Sage
Solution Sage

If I understood correctly, you want to see the X-Axis from July to June.

 

I considered from june:

 

Here I go to the query editor, and created a Month column and created a conditional column based on the Month. Here is the M code.

 

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

and change the Custom Column format to Number.

 

Now, you select the Date and then goto Modeling Tab and then select sort by Custom column.

 

 564564564.JPG

 

View solution in original post

3 REPLIES 3
rocky09
Solution Sage
Solution Sage

If I understood correctly, you want to see the X-Axis from July to June.

 

I considered from june:

 

Here I go to the query editor, and created a Month column and created a conditional column based on the Month. Here is the M code.

 

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

and change the Custom Column format to Number.

 

Now, you select the Date and then goto Modeling Tab and then select sort by Custom column.

 

 564564564.JPG

 

Thanks! This does the trick!

 

I have duplicated my date column and selected to show it as month in numbers, then I created the conditional column as you suggested, For clarity's sake, I then changed the output dates as followed "1 (Jul'17)", if it resembles some kind of date notation too much, Power BI will automatically change it to date format, and the sorting is altered. If I just use "1, 2, 3", it's kind of hard to know which month I'm talking about. I haven't  sorted my original date column with the fiscal month number, as the workaround above seemed sufficient. (I do not need to drill through to actual days.) 

The tables look like this now: 

Capture.PNG

 

Thanks for your help! Until we can change the internal calendar of Power BI, this is for me the workaround involving the least of complicated formulas and calculations.

glad you have sorted it out. 🙂

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.