Forum Discussion
X-Axis Sort by month AND year
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
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.
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!
14 Replies
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.
Hello everyone
Would anyone know if this is already possible by now ?
Kind regards
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!
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 ;)
Can you explain step by step, how did you do it ?
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,
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
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.
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
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
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
Solution is having it in two columns and dragging them into the Axis as explained in this other thread:
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??
This is possible. You just need to create a calculated with the month number on it and then sort your Month Name column by the month number column
