Forum Discussion
X-Axis Sort by month AND year
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!
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
