Power BI – Working with Date – Part I

I have been working with Power BI for few years and quite often someone asks me questions about Date functions in DAX (Data Analysis Expressions), that is the reason I am writing this post.

As prerequisites, your Power BI model needs to have a Date table, which will be used to create a couple of calculated column.

Month # = MONTH(‘Date'[Date])

This function returns the month as a number, such as, 1 for January, 2 for February, 3 for March to 12 for December.

Month-2Digits = FORMAT(‘Date'[Date], “MM”)

This function returns the month as a two (2) digits number, such as, 01 for January, 02 for February, 03 for March to 12 for December.

MonthName = FORMAT(‘Date'[Date], “MMMM”)

This function returns the month name, such as, January, February, March to December.

You need to Sort by Column “Month#”

MonthShortName = FORMAT(‘Date'[Date], “MMM”)

This function returns the month short name, such as, Jan, Feb, Mar to Dec.

You need to Sort by Column “Month#”

Year = YEAR(‘Date'[Date])

This function returns the year, such as, 2016, 2017.

YearMonthNo = FORMAT(‘Date'[Date],”YYYYMM”)

This calculated column returns the Year & Month as whole number, such as, 201703, 201704, 201705.

MonthYear = FORMAT(‘Date'[Date], “MMM-YY”)

This calculated column returns the Month & Year, such as, Jan-17 to Dec-17. You need to sort it by column YearMonthNo, see below.