A common requirement when reporting data with a Date axis is to be able to see the year and the month in a short form beside each other. Formats such as 2021 Jan, or Jan 2021, or even Jan 21, or 2021-01 are very popular. In Power BI, ideally, you should have a Date dimension with columns for this. However, if you don’t I’ll explain how you can create these formats easily using DAX.
My sample table just have a single column; Date as below;
If you are going to use the month NAME as part of the year-month combination, then you will need a label column and a code column. The label column is used to show the year-month combination in the visual, and the code column to sort the label column. let’s start with the label column;
To create a column for the year and month combined, one of the most helpful functions will be the FORMAT function. You can use the Format function like below;
Year-Month = FORMAT('Date'[Date],"YYYY MMM")
This can be a calculated column added to your table;
The output will be in the format of four digits for the year, and then a three-character month name after a space. That is because we used YYYY MMM as the format expression. You can use formats as below too;
Note that the M and Y can be lowercase too. Here is what they mean;
|MMMM||full month name||January|
|MMM||three-characters month name||Jan|
|MM||two-digit month number||01|
|M||One or two-digits month number (no leading zero)||1|
|YYYY||4-digit year number||2020|
|YY||2-digit year number||20|
This column above by itself usually isn’t enough, as the order of items might not be the right order if you used a format that produces the month name instead of the number. You would need a column that is The Year-Month code and you can use that to sort the Year-Month column.
This code generates a six-digit text for year and month combined which will be always in the right order if converted to a number;
Year-Month Code = FORMAT('Date'[Date],"YYYYMM")
Sort by Column
It doesn’t matter what format you used for the label column, the code column can sort it, by using the Sort by Column option. I explained more about this feature here in this article.
And this gives me the right ordered list of year-month values as below in any visual;
Hide the code column
Note that, it is recommended to hide the Year-Month code column from the report view. To learn more about what columns are a good candidate to be hidden read my article here.
I strongly suggest using a Date dimension (or calendar table) including all the columns for slicing and dicing by date-related attributes. A full script of a date dimension is available here in this article for you available to download;