Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
7 years ago
Solved

Adjusting the Date Axis on a Line Chart

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?

Need Help
  • sturlaws's avatar
    sturlaws
    7 years ago

    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

4 Replies

  • sturlaws's avatar
    sturlaws
    Icon for Resident Rockstar rankResident Rockstar
    7 years ago

    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's avatar
      Anonymous
      Not applicable
      7 years ago

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

      • sturlaws's avatar
        sturlaws
        Icon for Resident Rockstar rankResident Rockstar
        7 years ago

        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