How to create a DAX date table

So you’ve decided to create a date table in Power BI using DAX, good choice! If you’re new to DAX this blog will guide you through creating the table from start to finish. If you already know how to use DAX and just want to copy some calendar code go directly to the code section here.

If you’d rather watch a video explaining the steps involved then check out the accompanying YouTube video here:

If you’re still unsure why you need a calendar table check out this blog first: What is a calendar table and why should I create one in my Power BI project?

TURN OFF AUTOMATIC DETECTION OF DATES

Before you start writing any DAX you need to turn off the Power BI auto date/time option. This is switched on by default when you set up a Power BI project and creates a hidden date/time table for each date column. This is great if you need basic calendar columns but should be turned off if you’re creating your own data table.

  1. Turn the Auto date/time off in the Options dialog box (File menuoptions and settings options).
  2. Click on Data Load under the current file heading and untick Auto date/Time

CREATING THE DATE COLUMN

The minimum requirement for a Power BI date table is a table with at least one column containing dates. This date column has to span across full years, it cannot contain blanks or have missing dates and all dates must be unique. There are 2 ways to create this column using DAX:

– CALENDAR requires you to input a start and end date
– CALENDARAUTO searches all tables in Power BI to identify the minimum and maximum date, it then returns a column of dates starting from 1st Jan of the minimum date year and 31 Dec of the maximum date year.

This example uses the CALENDAR option:

  1. First you need to create a blank table in which to write the DAX.
    Go to the Modelling Menu – select New Table
  2. Next you use DAX to create the date column in the code box that appears
    Replace Table = with the following:

    Date Table = CALENDAR (DATE (2000, 1, 1), DATE (2023, 12, 31))

    Date Table is the table name and the code creates a date column spanning the years 2000 to 2023 (change to the years you require)

SET THE TABLE AS A POWER BI DATE TABLE

  1. Click on the table name in the fields panel (right hand side of screen)
  2. Click on the 3 dots that appear
  3. Select Mark as Date Table
  4. Select the date column from the drop down list

ADDITIONAL DATE INFO COLUMNS

Your date table is now ready to be used! However it will be far more useful if you add some additional date columns such as month name, year, quarter etc.

First add a new column to the date table:

  1. Click on the date table name in the fields panel (right hand side of screen)
  2. Click on the 3 dots that appear
  3. Select New Column

This creates a new column and opens a formula bar ready for you to name the column and add the DAX code. To start with we’ll add a column containing the day of the month. In the code below Day is the column name and [Date] is the date column previously created with the CALENDAR function:

Day column:

Day = Format([Date],”d”)

Next add a month column:

  1. Create a new column
  2. In the formula bar add the DAX
    Month = Format([Date],”MMMM”)

Now add a year column:

  1. Create a new column
  2. In the formula bar add the DAX
    Year = FORMAT([Date],”yyyy”)

DAX CODE FOR NEW DATA INFORMATION COLUMNS

Now you have a far more useful date table with a day, month and year column. There are many more date information columns you can add, which ones you use depends on your preference and report requirements.

Below is a list of DAX code that can be used to create 19 date information columns, you can pick and choose which columns you want to add to your table. For performance purposes you should only create columns that you will be using in your report.

Quarter – the quarter of the calendar year (Q1, Q2, Q3, Q4)

Quarter = "Q"&Format([Date], "q")

Month Number – calendar Month Number, Jan = 1, Feb = 2 etc

Month Number= Format([Date],"M")

Month Short – short month name i.e Jan, Feb, Mar

Month Short= Format([Date],"MMM")

Month Letter – first letter of the month name

Month Letter= LEFT(Format([Date],"MMMM"),1)

Year Short – last 2 numbers of the year e.g 10 for the year 2010

Year Short= FORMAT([Date],"yy")

Month Year – short month name and year e.g Jan 2000

Month Year= Format([Date],"MMM") & " " & FORMAT([Date],"yyyy")

Year Month Number – year and month number (used to order months over years) i.e 200012 for Dec 2000

Year Month Number = FORMAT([Date],"yyyy") & Format([Date],"MM")

Financial Month Number – creates the month number based on financial year.
Here the example sets the financial year to start in April, change the variable
FinancialStartMonth to the month number that your financial year starts at.

Financial Month Number =
VAR FinancialStartMonth = 4
RETURN
if(Month([Date]) >= FinancialStartMonth, 
Month([Date]) - FinancialStartMonth + 1, 
Month([Date]) + (12 - FinancialStartMonth) +1)

Financial Quarter – creates the quarter based on the financial year.
Here the example sets the financial year to start in April, change the variable
FinancialYearStartMonth to the month number that your financial year starts at.

Financial Quarter=
Var FinancialYearStartMonth = 4
var Mn = MONTH([Date])
RETURN
if(Mn < FinancialYearStartMonth,
"Q"&FORMAT(ROUNDUP((Mn - FinancialYearStartMonth + 13)/3,0),""),
"Q"&FORMAT(ROUNDUP((Mn - FinancialYearStartMonth + 1)/3,0),""))

Financial Year – creates the financial year, Here the example sets the financial year to start in April, change the variable FinancialYearStartMonth to the month number that your financial year starts at.

Financial Year =
VAR FinancialYearStartMonth = 4
RETURN
IF(MONTH([Date])>= FinancialYearStartMonth,YEAR([Date]), YEAR([Date])-1)

Financial Year Display – creates a user friendly financial year display e.g 2000-2001.
Here the example sets the financial year to start in April, change the variable
FinancialYearStartMonth to the month number that your financial year starts at.

Financial Year Display =
VAR FinancialYearStartMonth = 4
RETURN
IF(MONTH([Date])>=FinancialYearStartMonth, 
YEAR([Date])&"-"&YEAR([Date])+1,
YEAR([Date])-1&"-"&YEAR([Date]))

Day of Week – day of week name e.g Monday

Day of Week = Format([Date],"dddd")

Day of Week Short – short day of week e.g. Mon, Tue, Wed

Day of Week Short = Format([Date],"ddd")

Day of Week Letter – Shortened day of week e.g M for Monday Th for Thursday

Day of Week Letter = SWITCH(Format([Date],"dddd"), 
"Monday","M", "Tuesday","T", "Wednesday","W", 
"Thursday","Th", "Friday","F", "Saturday","Sa", "Sunday","S")

Day of Week Number – Number of the day of week where Monday = 1. Change the 2 in the code to 1 to start the week on a Sunday.

Day of Week Number =WEEKDAY([Date],2)

Weekend – Sets the field to a Yes if the day is a Saturday or Sunday and No for all other days. If your weekends are Friday and Saturday change the code accordingly.

Weekend = SWITCH(Format([Date],"dddd"), 
"Sunday", "Yes", "Saturday", "Yes","No")

Week Number – The week of year number, this example is where the week starts on a Monday. If you want the week to start on a Sunday change the 2 to 1.

Week Number= WEEKNUM([Date],2)

Processing…
Success! You're on the list.

Cover photo by Adam Tinworth on Unsplash

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

%d bloggers like this: