Adding Year Column to DateMaster Table in DAX

Creating a Year Column in "YYYY" Format in DAX

The goal is to create a new column in the DateMaster table to represent the year in the "YYYY" format using DAX. Below is the step-by-step breakdown and implementation.

Requirements

1. Calendar Table: We already have a DateMaster table created using the CALENDAR function.
2. New Column: Add a column to format the year as "YYYY".

Step-by-Step Solution

DAX Code

-- Importing necessary functionalities through CALENDAR function
DateMaster = 
    CALENDAR(DATE(2005, 1, 1), DATE(2030, 12, 31))

-- Adding a new custom column 'Year'
DateMaster = 
    ADDCOLUMNS(
        DateMaster,
        "Year", FORMAT([Date], "YYYY")
    )

Explanation

1. Creating the Calendar Table:

   DateMaster = CALENDAR(DATE(2005, 1, 1), DATE(2030, 12, 31))

   • Utilizes the CALENDAR function to create a date range from January 1, 2005, to December 31, 2030.

2. Adding the Year Column:

   ADDCOLUMNS(
       DateMaster,
       "Year", FORMAT([Date], "YYYY")
   )

   • ADDCOLUMNS function is used to add a new column to the DateMaster table.
   • The FORMAT function converts the date into the "YYYY" format.

Practical Example

Before Adding the New Column

-- Without Year Column
DateMaster = 
    CALENDAR(DATE(2005, 1, 1), DATE(2030, 12, 31))

After Adding the New Column

-- With Year Column
DateMaster = 
    ADDCOLUMNS(
        CALENDAR(DATE(2005, 1, 1), DATE(2030, 12, 31)),
        "Year", FORMAT([Date], "YYYY")
    )

Conclusion

By using the CALENDAR function followed by ADDCOLUMNS and FORMAT, we can seamlessly add a "Year" column to our DateMaster table in the desired "YYYY" format. This ensures our dates are clearly structured and easy to analyze based on the year.

For more advanced DAX learning and professional development, consider exploring courses available on the Enterprise DNA platform.