Creating a Date Dimension Table in Power BI

By:   |   Comments (8)   |   Related: > Power BI


How can we create a date dimension table in Power BI? We need data such as month name, year, financial quarter, etc.  Also, how can we access this data directly with DAX?


A date dimension is an integral part of a data warehouse. A date dimension will have a range of dates with attributes such as Month Name, Year, Financial Quarter, Financial Semester and Financial Year.

In this tip, I will detail a method to create a Date Dimension in Power BI.

Solution Overview

In this approach, I will make use of a Power BI calculated table to generate date values. Also I will be adding attributes such as Month Name, Financial Year, Financial Semester and Financial Quarter with the help of DAX.

Calculated Tables

In a calculated table, the table values are generated by Data Analysis Expression (DAX) and the values are stored in the Power BI model.

DAX Calendar Function

The calendar function returns a table with a single column that contains a continuous set of dates. The start and end date range will be supplied as parameters.

The following formula returns a calculated table with dates between January 1st, 2005 and December 31st, 2015.

=CALENDAR (DATE (2005, 1, 1), DATE (2015, 12, 31))

Creating Date Dimension Calculated table

In the Power BI desktop application, let’s create a new table.

Creating new calculated table - Description: Creating new calculated table

Once the table has been created, now let’s add the DAX expression to generate desirable date values. I will be using the Calendar function to generate date values. In this example, I will be creating date values between 1st January 2015 and 31st December 2020.

=CALENDAR (DATE (2015, 1, 1), DATE (2020, 12, 31))

Use DAX Function to calculate date values - Description: Use DAX Function to calculate date values

This function accepts start and end dates as parameters. I have provided the start and end date as “01/01/2015” and “31/12/2020”. Once updated, the Power BI will generate the date values in the query.

Calculated table with dates between 2015 and 2020 - Description: Calculated table with dates between 2015 and 2020

Adding Attributes to Date Dimension

Let’s add a year attribute to the table and the DAX expression to calculate the year which is “Year([Date])”.

Adding new column for attribute - Description: Adding new column for attribute

Using the Year function to calculate Year - Description: Using the Year function to calculate Year

As above, now let’s add more attributes to the calculated table. I have provided the DAX expression for each attribute below.

Year = YEAR([Date])

Day = Format([Date],"DDDD")

DayofMonth = DAY([Date])

MonthofYear = MONTH([Date])

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

QuarterofYear = ROUNDUP (MONTH([Date])/3,0)

Quarter = "Q" & [QuarterofYear] & " " & [Year]

OrdinalDate = DATEDIFF([Year]&",1,1",[Date],DAY)+1

DayofWeek = WEEKDAY([Date],2)

WeekEnding = [Date] + (7- [DayofWeek])

The fully extended DateDimension table can be found below.

Adding other attributes to the table - Description: Adding other attributes to the table


The data in the calculated table is self-contained and source independent. Based on the configuration of start and end date, the date range can be extended easily. Hence this approach is ideal for reporting and analysis purposes.

Next Steps

Learn more about Power BI in this 3 hour training course.

Click here to start the Power BI course

sql server categories

sql server webinars

subscribe to mssqltips

sql server tutorials

sql server white papers

next tip

About the author
MSSQLTips author Nat Sundar Nat Sundar is working as an independent SQL BI consultant in the UK with a Bachelors Degree in Engineering.

This author pledges the content of this article is based on professional experience and not AI generated.

View all my tips

Comments For This Article

Sunday, May 17, 2020 - 8:29:13 AM - dominick Back To Top (85680)

Thanks so much for doing what you do.  This saves me so much time and not to mention stress!  it worked!  

Sunday, July 1, 2018 - 7:23:55 AM - sarfaraz Back To Top (76493)

 Two quesions:


1-How to directly calculate only the 12 months?

2-How to change date format and name of weeks, months, years to other languages. like Arabic, Persian or Chinese

Wednesday, June 20, 2018 - 4:48:23 PM - Ray Giacalone Back To Top (76259)

 HI NAT, THX FOR THIS POSTING!  I am currently all over DAX expressions and the computed calendar table at present...and am currently trying to come up with the most elegant way to write a fiscal year computed calendar table.  Thx again for the start.

Monday, March 5, 2018 - 12:54:59 PM - Monica Back To Top (75349)


Thank you for this helpful post!

Friday, January 5, 2018 - 2:29:03 PM - Richard Krol Back To Top (74843)

Great Dimension table for dates, but here is a "GOTCHA" that some people may not know about with 365 for EE, when using 12 hours and in US.  

Dates (such as InvoiceDate) on a sales invoice line or order line, are written in PM, not AM.  So when you make your DateDim.Date column in the table, it will add the dates and times as AM.  In order to link up with your 365 EE table dates which are in PM, add another column at the end of your DateDim table, and use this formula:

DateInvLink = [Date]+TIME(12,0,0)

This will add 12 hours to your date, so it's reflected as AM and not PM.  This way, if you have some dates in AM and some in PM for different application data, you have a framework to select either PM or AM.

Then, instead of making a relationship with your 365's table to the DimDate.Date column, use the DimDate.DateInvLink.  This way, your PM date in 365, matches your PM date in your DimDate table.


Wednesday, November 8, 2017 - 4:47:10 PM - Joan Hauff Back To Top (69450)


Wednesday, May 24, 2017 - 10:09:08 AM - Jeff Moden Back To Top (56007)

Great tip.  Thanks for taking the time to put this post together.


I do have a question with the overall notion, though.  It's normally fairly important that there be a company Calendar table, including Holidays and other important nuances, already available in the database side of the house.  It seems like creating another Calendar table would cause the problem of having to accurately and certainly maintain more than one Calendar table.  Is there a way that Power BI could make use of that normally existing table so that there's only a single, accurate source of company calendar information?



Wednesday, May 24, 2017 - 9:05:19 AM - Suresh Nadesan Back To Top (56002)

 Hey, what you do if you have college semester and year but no dates. And with that compare year to year and cost. Please give and example.


get free sql tips
agree to terms