GROUP INTO MONTH

[baBI123]

Hello POWER BI family, 

I have a question:  How can I group this chart (see attachment) so that the values on the x-axis are grouped by month?? 

Thanks in advance

[Anonymous]

Not sure if this has changed since this post, but for those looking, the correct way to do it now is:

1. Go to the visulisation field where the date will be listed under the "Shared axis"
   
2. Click on the arrow on the right, then change from the name of your field (will be ticked) to "Date Hierarchy"
   
3. You should now have the options of year etc under your field, in this example I will label as year and month. Day can stay as its lower in the hierarchy, but get rid of Quater or it will get in the way, so click the x to remove it.
   
4. You will now see it sorted by year, to get it to month right click on the chart (anywhere on the chart that is blank) and choose "Expand to next level"
   

You should now have your chart with year and month. Of course if its only one year you can remove the year where we removed the quater etc. Hope this helps anyone in future.

[v-yuezhe-msft · Accepted solution]

@baBI123,

If you create relationship between the SHOP WO TAT table and a calendar table  using date field, drag the date field of  the calendar table to X-axis of your chart, then you are able to get date hierachy and remove Year  & Quarter & Day as mehaboob557' s post.

Another method is to create a MonthName field in your SHOP WO TAT table using DAX below, then drag the MonthName field to X-Axis of your chart.

MonthName = SWITCH(MONTH('SHOP WO TAT'[Entry Date]),1,"January",2, "February",3,"March",4,"April", 5,"May",6,"June",7,"July",8,"August",9,"September",10,"October",11,"November",12,"December")

Regards,
Lydia

[mehaboob557]

Hi,

You can do it my removing the year in the date heirarchi in the values section

     =====>    

This may help you to group in months

[baBI123]

HI @mehaboob557 Unforunately, I don't have that option... any other ideas?

[thomasronn]

Make sure your Entry Date column is set to datatype Date/Time

[mehaboob557]

Hi @baBI123,

Can you share me the sample pbix file .

SO that i can check.

[baBI123]

Unfortunately, I can't share this file with you @mehaboob557because it has private company information... Thank you for your help. It is still not working..

[v-yuezhe-msft]

@baBI123，

Have you checked my methods? The two methods work in my environment.

Regards,
Lydia

[baBI123]

your second suggestion worked @v-yuezhe-msft one more question, how can I get them to go in order (ex: JAN, FEB, MARCH) ? Right now the month names are sorted in alphabetical order 😞 

Thanks!

[mehaboob557]

Hi ,

I am just posting a ptocedure to sort irrespective of your table names.

Go to data (left hand side button) and click

Then go to the table to be sorted (right hand side)

You will need three columns in this calendar table for month
create

1 Main column (to set up the link column for Invoice Date)
CalendarInv = CALENDAR (MINX(Header, Header[Invoice Date]), today())
2 Column for month in text format
MonthT = FORMAT( CalendarInv[Date], "MMM")
3 Column for month in number format
MonthN = Month (CalendarInv[Date])

Then go to field list on the right hand side Click on MonthT

then go to modelling / sort by column and select MonthN
Reference : https://community.powerbi.com/t5/Desktop/Sorting-by-Month-as-Text-Jan-Feb-Mar-etc/td-p/9237