Adjusting the Date Axis on a Line Chart

[Anonymous]

Hopefully a fairly simple question. For a project I am working on, I need to create a line chart with months being the x-axis, but I need it to start with October and run through Septmber to represent our businesse's fiscal year. How can this be done?

[v-lili6-msft]

hi, @Anonymous 

You could use Sort By column in power bi to get it.

Use your fiscal [year-month] column as the "By column", If there is no [year-month] column in the table, just add it manually.

https://radacad.com/sort-by-column-in-power-bi

Best Regards,

Lin

[sturlaws · Accepted solution]

It is a simple solution for this. In the modelling ribbon of PBI Desktop, there is a "Sort by column"-button. Say you have date table, and in that table you have a column with the month name, e.g. January, February,...

The only thing you might be missing is how the months should be ordered. You could use the "Enter data"-functionality found in the home ribbon of PBI Desktop to type in month name and the order of the month. Then from your date table create a new column which is a lookup to this manually entered table to get the month sorting. Select you month name column in the date table and sort it by the new column in the date table.

Or you can use a little dax like this in your date table:

MonthNumberFiscal =
IF (
    MONTH ( vDate[Date] ) < 10;
    DATEDIFF (
        DATE ( YEAR ( vDate[Date] ) - 1; 10; 1 );
        STARTOFMONTH ( vDate[Date] );
        MONTH
    );
    DATEDIFF (
        DATE ( YEAR ( vDate[Date] ); 10; 1 );
        STARTOFMONTH ( vDate[Date] );
        MONTH
    )
) + 1

her as an example: report

[Anonymous]

@sturlaws  Can you give me an example of how to create the lookup column? 

[sturlaws]

here is one example: example

but excatly how to do it will depend on your data and desired output. Do you have data for more than one fiscal year? Should your slicers have values like 2017/2018, 2018/2019, ...? Should the labels be Oct, Nov,....,Sep or should they be Oct-18, Nov-18,..,Jan-19,...Sep-19?

There is one small issue to be aware of. The column you use for the lookup can not be sorted by the column resulting from the lookup.

br,

S