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’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.
- Turn the Auto date/time off in the Options dialog box (File menu — options and settings -options).
- 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:
- First you need to create a blank table in which to write the DAX.
Go to the Modelling Menu — select New Table - 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
- Click on the table name in the fields panel (right hand side of screen)
- Click on the 3 dots that appear
- Select Mark as Date Table
- 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:
- Click on the date table name in the fields panel (right hand side of screen)
- Click on the 3 dots that appear
- 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:
- Create a new column
- In the formula bar add the DAX
Month = Format([Date],”MMMM”)
Now add a year column:
- Create a new column
- 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)Originally published at http://bi-jo.com on December 1, 2020.
Photo by Adam Tinworth on Unsplash
