Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Christian
New Member

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:

Chart1.JPG

 

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

 

 

2 ACCEPTED SOLUTIONS
asocorro
Skilled Sharer
Skilled Sharer

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.

 

 

Connect with me in LinkedIn: https://pr.linkedin.com/in/adolfosocorro
Follow me on Twitter: https://twitter.com/AdolfoSocorro

View solution in original post

ashishrj
Power Participant
Power Participant

@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!

MonthYear.png

View solution in original post

13 REPLIES 13
marslan
Frequent Visitor

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??

 

marslan_1-1665068411320.png

 

 

fernandev
New Member

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
New Member

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

Maxim Zelensky
excel-inside.pro
noshali
New Member

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

 

ashishrj
Power Participant
Power Participant

@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!

MonthYear.png

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

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
Skilled Sharer
Skilled Sharer

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.

 

 

Connect with me in LinkedIn: https://pr.linkedin.com/in/adolfosocorro
Follow me on Twitter: https://twitter.com/AdolfoSocorro

Hello everyone

 

Would anyone know if this is already possible by now ?

 

Kind regards

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.