Beginner’s guide to DAX in Power BI: Creating a Date Table

This is the DAX code for our table. Below, I’ll explain what each section does.

Date Table =
var dates = CALENDAR(MIN(financials[Date]), MAX(financials[Date]))
RETURN

ADDCOLUMNS(dates,
    “Year”, YEAR([Date]),
    “Quarter”, QUARTER([Date]),
    “Month”, MONTH([Date])
)

It’s the VAR function that enables us to create a custom variable. To return to using DAX, after you’ve created your variable, you type RETURN. Now we’re free to use our variable as many times as needed, which translates to helping us write cleaner and more efficient code. If you have to make a change to the variable, you only need to change it once, in the VAR statement, instead of every time you use it throughout the rest of your DAX measure or calculated column.

In our VAR expression, we use CALENDAR() ,MIN(), and MAX() to create a calendar based on the first and last dates of our date column in the Financials table.

Now let’s get into creating a new table in DAX. We use ADDCOLUMNS() to add the columns to our calendar we created in our VAR expression. The columns we want to add are Year, Quarter, and Month. The argument order for ADDCOLUMNS() is ADDCOLUMNS(Table, Name, Expression). So, following our name for each column and the table we want to put these columns into is our formula for each column.

These are pretty straightforward as YEAR(), QUARTER(), and MONTH() return the specified date part of the Date field, so they output the year, quarter, and month portions of the Date field, respectively. Now that we’ve finished adding the columns we want, hit the Enter key or the checkmark to confirm the new table.

As you can see, our new date table has been populated! To use this separate table, we need to create a relationship with the main Financials table. In the model view, drag the Date field from the Date table to the Date field in the financials table to create a relationship.

Bringing Tables Together: Tableau Relationships

This dialog helps us visualize the relationship we are creating between the two tables. Note that a one-to-many/many-to-one relationship is the most common type of relationship you can create in Power BI. In this type of relationship, the ‘one side’ is a table that has one instance of a value in the selected column, and the ‘many sides’ can have multiple instances of the same value in the selected column. Now we can analyze our financials table using our newly created month, quarter, and year columns in the date table. To further customize your analysis, continue to add date parts to your date table! 

Thanks for reading,
Juan Carlos Guzman