Generate Year and Month Combinations in Power BI Report using DAX

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.

Video

Sample table

My sample table just have a single column; Date as below;

Label column

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;

CharacterDetailsSample
MMMMfull month nameJanuary
MMMthree-characters month nameJan
MMtwo-digit month number01
MOne or two-digits month number (no leading zero)1
YYYY4-digit year number2020
YY2-digit year number20

Code column

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.

Date Dimension

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;

Reza Rad on FacebookReza Rad on LinkedinReza Rad on TwitterReza Rad on Youtube
Reza Rad
Trainer, Consultant, Mentor
Reza Rad is a Microsoft Regional Director, an Author, Trainer, Speaker and Consultant. He has a BSc in Computer engineering; he has more than 20 years’ experience in data analysis, BI, databases, programming, and development mostly on Microsoft technologies. He is a Microsoft Data Platform MVP for nine continuous years (from 2011 till now) for his dedication in Microsoft BI. Reza is an active blogger and co-founder of RADACAD. Reza is also co-founder and co-organizer of Difinity conference in New Zealand.
His articles on different aspects of technologies, especially on MS BI, can be found on his blog: https://radacad.com/blog.
He wrote some books on MS SQL BI and also is writing some others, He was also an active member on online technical forums such as MSDN and Experts-Exchange, and was a moderator of MSDN SQL Server forums, and is an MCP, MCSE, and MCITP of BI. He is the leader of the New Zealand Business Intelligence users group. He is also the author of very popular book Power BI from Rookie to Rock Star, which is free with more than 1700 pages of content and the Power BI Pro Architecture published by Apress.
He is an International Speaker in Microsoft Ignite, Microsoft Business Applications Summit, Data Insight Summit, PASS Summit, SQL Saturday and SQL user groups. And He is a Microsoft Certified Trainer.
Reza’s passion is to help you find the best data solution, he is Data enthusiast.

Leave a Reply