DAX Year-Month sorting

In this example, a DAX calculated column is created to support sorting of the Calendar Yr-Mo column.

  1. Access the Date table in Data view and click on New Column from the Modeling tab.
  2. Enter the following DAX expression to create sorting column at the grain of calendar months across years:
DAX Sorting Calculated Column
  • The YEAR() function is applied against the Date column and multiplied by 100 to add two digits:
    • MONTH() returns a value of 1 to 12 and this is added to the six digit number
Although this approach is simple to implement and inexpensive in resource usage, the values of the new column are not sequential thus limiting the use of the column in Time Intelligence measures (for example, trailing 3 months). S equential surrogate key columns for each grain of the date dimension, including fiscal calendars, is an essential component to robust Date Intelligence logic. See Chapter 6, Getting Serious with Date Intelligence for examples of implementing these columns via both SQL and M expressions. 
上一章目录下一章