X-Axis Sort by month AND year

[Christian]

Hello.

I just started using Power BI and I am trying to discover the functions. Now my problem is that I cannot group or arrange my date the way like I had in my Excel worksheet.

My data table has a date with day-month-year and a value that I want to sum up ... this should look like this:

I tried using extra columns with just month and year, but I dont get it like I had it in excel.

Hope you understand the german excel-chart but it should be easy to recognize it 🙂

Greetings

[asocorro · Accepted solution]

In Power BI it's not possible to have a hierarchy like that in the X axis.  To simulate it, you will need to create a calculated column that gives values such as, for example, 2015-Jan, 2015-Feb, etc., and then set its Sort By Column property to the column with your actual dates.

[marslan]

I tried the solution but it shows me this error, the reason I believe is because I have duplicate, becuase my original data has Year-Month-Date (since a single month contains various dates when concatenated to Just Year and Month, it shows duplicate). Any idea how to fix this issue??

[fernandev]

Solution is having it in two columns and dragging them into the Axis as explained in this other thread:

https://community.powerbi.com/t5/Desktop/Stacked-Column-Chart-Show-month-date-hierarchy-split-by-yea...

[noshali]

Hi

My fiscal year is July-June. I have sales data in excel sorted with date and the graphs in excel look fine as I want to show sales data from July-June. Now when I import the data in PBI. the visualization (graph) is from Jan-Dec.

Please help on how to fix this.

Thanks

[hohlick]

Hi noshali

It is better if you open your own thread to get the answer.

but anyway, you can add custom column to indicate, for example, July as first month, etc., then conactenate your fiscal year with this month number to one, for example "2014-15/01", "2014-15/02" etc.

Or, try to use "StartOfMonth" as axis

[noshali]

This is an issue as my fiscal year is July-June. Excel show the data fine and graphs look great as the first value is July 2015 and the last value is June 2016. But in power BI. The graphs are from Jan to Dec.

What is the best way to deal with it??? as my fiscal year is always going to be what it is.

Regards,

[Kaycee1]

Solution:

Add a Year Column: FieldYear = YEAR(Date)

Add MonthColumn:  FieldMonth =  MONTH(Date)

THEN ADD SORTORDER column using the two extra columns created above and in this order :  SortOrder =                     CONCATENATE(FieldYear,FieldMonth)

Key now is to change the Cortorder datatype to Whole Number (accept the warning that stores your data differently)

Next, Sort Datefield by the 'SortOrder' COLUMN YOU HAVE JUST ADDED and bang!

Good luck

[Anes]

Based on "Kaycee1"'s answer, I ended up with this:

SortMonth = CONCATENATE(YEAR(TableName[Date]),FORMAT(MONTH(TableName[Date]),"00"))

Using Kaycee1's answer, I had a problem where 201912 (2019 Dec) was considered greater than 20201 (2020 Jan). So I changed the format of Months to be two digits by FORMAT(TableName[Month], "00"), so instead of 1, you'll have 01.

[Kaycee1]

Solution:

Add a Year Column: FieldYear = YEAR(Date)

Add MonthColumn:  FieldMonth =  MONTH(Date)

THEN ADD SORTORDER column using the two extra columns created above and in this order :  SortOrder =                     CONCATENATE(FieldYear,FieldMonth)

Key now is to change the Cortorder datatype to Whole Number (accept the warning that stores your data differently)

Next, Sort Datefield by the 'SortOrder' COLUMN YOU HAVE JUST ADDED and bang!

Good luck

[ashishrj · Accepted solution]

@Christian By creating few calculated columns you can achieve something like as shown below. Also you can format the way you want to display year-month. For instance now its 2015-07. You can also use something like July 2015 or 2015 July or 07/2015..etc. But I haven't found the grouping one you mentioned above. Hope this helps!

[SDK0415]

Can you explain step by step, how did you do  it ?

[Christian]

This isn't the solution I wanted to hear, but I already thought that this will be the only possibility. Thank you anyway for both of your replies.

But Power BI is still in development so I haven't give up hope that this will be integrated  someday 😉

[asocorro · Accepted solution]

In Power BI it's not possible to have a hierarchy like that in the X axis.  To simulate it, you will need to create a calculated column that gives values such as, for example, 2015-Jan, 2015-Feb, etc., and then set its Sort By Column property to the column with your actual dates.

[BVDO2]

Hello everyone

Would anyone know if this is already possible by now ?

Kind regards