Dates Grouped by Month/Year - some months not labled on axis

[mmace1]

Hi - so my chart is missing some of the X-axis lables for month/year.

I could switch it from Continuous to Categorical, but then things are out of order.

My "Month & Year" field is from my Calender table, and is calcualted via:

date(year('Calender Table'[Date]),month('Calender Table'[Date]),1)

If it makes a difference.

Seems like a pretty simple thing, but what am I missing?  Thanks!

[nchambe]

Create a DateKey and then make sure you sort your "Month" values by month number as described in this post. That will make sure your months show up in the correct order in visuals.

I would also recommend using the values from the DateKey on the axis (and in any filters) instead of the values from your data. It allows for consistent, continuous dates even when you don't have data for a date. Just be sure to create a relationship between your data's date field and the date field in the DateKey so that it works.

[mmace1]

Thanks, but this is already all coming from a Calender table (aka DateKey). 

[nchambe · Accepted solution]

Perfect. You'll need to use the "categorical" sort to get all labels. Then to get them in the correct order:

1. select the data field (MMYYYY) from under the fields pane on the far right
2. Under the modeling tab in the ribbon, sort by a YYYYMM formatted column from your datekey
   YMonth = format(DateKey[Date],"YYYYMM")
   

[mmace1]

Thanks - the 'Sort by Column' is what I was missing.  Though, mine is greyed out for some reason...

I tried changing my month/year to the way yours is calculated (using format, whereas I was using date), but that doesn't seem to make a difference.

[v-yulgu-msft]

Hi @mmace1,

Make sure you have selected the column which needs to be sorted like month name rather than just select the visual.

Also, in "Data" view mode, select the certain column, is "Sort by Column" available?

Best regards,

Yuliana Gu