Month and Year Combination in Power BI Axis/Slicer – DAX

  • Published
  • Posted in Power BI
  • Updated
  • 2 mins read
  • Tagged as

Recently, we are asked to achieve the date axis with month and year in short form, such as
Aug-21, Aug 21. To get this as axis, a column should be present in the date dimension. Let us learn how to create this in DAX and use.

Sample Date Dimension Data:

Now, let us create the column that is desired using the DAX. There is 2 ways to create, focus on one and next.

  1. First method – Use the below DAX function

Month-Year = FORMAT(‘Date'[Date], “MMM”) & “-” & FORMAT(‘Date'[Date], “YY”)

This formula comprises of 2 parts,

FORMAT(‘Date'[Date], “MMM”) returns the month in format of MMM, such as Jan, Sep, Aug
FORMAT(‘Date'[Date], “YY”) returns the year in the format YY, such as 20, 21, 22

And the resultant of this combination is MMM-YY, such as Jan-20, Jan-21, Aug-21

2 Second method – Use the below DAX function

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

This is very straight forward compared to the previous method, Format DAX function takes care of the resultant directly as MMM-YY, such as Jan-20, Jan-21, Aug-21

Now, let us put this in Slicer visual as see the result visually

The data is not presented as a sequence of calendar months, so this is not required. Even if we sort this the data will be sorted alphabetically. To get this data sorted we need the create a code column and then this can be used to sort the Month-Year column.

Creating Code column:

MonthYearCode = FORMAT(‘Date'[Date], “YY”)*100 + FORMAT(‘Date'[Date], “MM”)

This overall result will be in the format of YYMM, such as 1901, 1902

The above DAX is made up of 2 parts, we can directly create the required result in one short as well (shown below).

MonthYearCode = FORMAT(‘Date'[Date], “YYMM”) – Which is the combination of both year and month.

Sort the Month-Year Column using the MonthYearCode column.

Select the Month-Yearcolumnfrom the date dimension, from the column tools option from the ribbon select the sort by column > MonthYearCode.

This means the Month-Year column will be sorted by the MonthYearCode column.

Now, see the slicer visual, the data will be ordered in chronological order (shown below).

Hope you find this useful, thanks.

Srikanth Kotapati
Data Analyst
Addend Analyst

Addend Analytics is a Microsoft Gold Partner based in Mumbai, India, and a branch office in the U.S.

Addend has successfully implemented 100+ Microsoft Power BI and Business Central projects for 100+ clients across sectors like Financial Services, Banking, Insurance, Retail, Sales, Manufacturing, Real estate, Logistics, and Healthcare in countries like the US, Europe, Switzerland, and Australia.

Get a free consultation now by emailing us or contacting us.

Email Us Or Contact Us