Power bi create a date table – Complete tutorial

In this power bi tutorial, we will discuss power bi create a date table. And also we will discuss the below points:

• What are the requirements to create a date table in power bi?
• What are the several techniques to add a date table to your model?
• Power bi creates date table Dax.
• How do you automatically add dates in power bi?
• Power bi creates a date table in the power query.
• Power bi creates a date table hierarchy.
• Power bi create date table with hours
• Power bi creates a date table with month and year
• Power bi create date table until today

Why to create a date table in power bi?

Here we will see, what are the requirements to create a date table in power bi.

To work with the DAX time intelligence function, the Power Bi model requires at least one date type column.

Few points to remember:

• The data table must have a column of data type date (or date/time), known as the date column.
• The date column must have unique values and it must not have blank.
• And the Date column must not have any missing date.
• The date column must have spanned a full year.
• And a year isn’t necessarily a calendar year i.e. January – December
• The date table must be marked as the data table

Read: Power bi desktop vs Power bi service vs Power bi premium

Create date table in Power BI Desktop using DAX

There are a couple of functions in DAX to create a Date table in Power BI. Such as, CALENDAR and CALENDARAUTO.

CALENDAR requires the set of dates, whereas CALENDARAUTO searches among all the dates in the data model and automatically finds the first and last year reference within the data.

Now we will see how to create a data table on Power BI Desktop by following these easy steps:

Step-1:

First, we have to create a Date table naming as Date. On Power BI Desktop, go to modeling > New table > put this below code.

Date = Calendar( Date(Start date), Date(End date))

Here we create a date table having dates from 1/1/2020 to 31/12/2021. We can see a date column appears on the data model.

By expanding the date column, we can see a date hierarchy having Year, Quarter, Month, and day.

We can see the date table in the Data view.

Step-2:

Now we will create a column for the month using DAX expression:

Month = Format('Date'[Date],"MMMM")

It will create a Month column on the data model.

Step-3:

Similarly, we will create another two-column for year and quarter using these DAX expressions:

For Quarter:

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

For year:

Year = Format('Date'[Date], "YYYY")

Now, we can see all the data tables should look like this:

This is how to create a Date table in Power BI Desktop using DAX expression.

How to add a date table to your model in Power BI

Here we will see the several techniques to add a date table to your model, These are:

• Use auto date or time option
• Using power query to connect to a date dimension table
• Using power query to generate date table
• By using DAX to generate a date table
• By using DAX to clone the existing table

Use Auto date or time

• The auto date and time option deliver fast convenient and easy to use time intelligence.
• Whereas the power bi report authors can work with time intelligence when grouping filtering and drill down through calendar time periods.
• Power bi recommended that you keep the Auto date and time option enabled only when you work with calendar time periods and when you have simplistic model requirements with time.
• Auto date/time option can also be convenient when creating ad hoc models or performing data exploration.
• And this approach doesn’t support a single date table design that can propagate filters to multiple tables.

Using power query to connect to a date dimension table

• We recommended you use the date table as the source of your model date table when your data source already has a date table.
• The case when you are connecting with the data warehouse, as it will have a date dimension table. In this way, your model leverages a single source of truth for the time in your organization.
• If you are developing the direct query model and your data source doesn’t include a date table, so it is strongly recommended you add a data table to the data source.
• And also it should meet all the requirements of modeling in a date table.
• We can use the power query to connect to the date table. And in this way, the model calculation can leverage the DAX time intelligence capabilities.

Generate date table by using power query

• We can generate a date table by using a power query.
• If the user doesn’t have a data warehouse or other consistent definition for the time in your organization, which is considered using a power query to publish a data flow.
• Then all the data modelers connected to the dataflow to add date tables to the models.
• The power bi dataflow becomes the single source of truth for a time in your organization.

By using DAX to generate a date table

• We can generate a date table in your model by creating a calculated table using either the CALENDER DAX function or CALENDERAUTO DAX function.
• Each function will return a single column table of dates.
• We can extend the calculated table with calculated columns to support your date interval grouping and filtering requirement
• When you want to define a date range, you can use the Calendar function. Then pass the two value i.e. the start date and end date
• The start date and end date can be defined by other DAX functions, like MIN(Sales[Order Date]) and MAX(Sales[Order Date]).
• When you want the date range to automatically encompass all dates stored in the model, by using the CALENDERAUTO function.
• We can pass in a single optional parameter that is the end month of the year.
• The CALENDERAUTO function is helpful because it ensures that full years of dates are returned, it is the requirement for a marked date table.
• We don’t need to manage to extend the table to future years when data refresh completes, it triggers the recalculation of the table in power bi.
• And the recalculation table’s date range when dates for a new year are loaded into power bi model.

By using DAX to clone the existing table

• When your power bi model already has date tables and then you need an additional date table, we can easily clone the existing date table.
• We can clone a table by creating a calculated table.
• Then the calculated table expression is simply the name of the existing date table.

Read: Power BI Clustered Column Chart

Power BI date table relationships

In Power BI, we will work with data models that contain data from multiple tables that must be connected through relationships.

Well-prepared tables connected by relationships are required to function together to allow visualizations to be sliced and diced by data from different tables.

Relationships between tables can have three types of Cardinality.

• Many to One(*:1)- It means a column in one table can have one instance of a value and another column may have numerous instances of the value.
• One to One(1:1)- It means the both table relational column has only one instance of a value.
• Many to Many relationship- As two tables have a many-to-many relationship it removes requirements for unique values in tables.

Using the previous Date table, we will see how to create relationships between date tables in Power BI.

Step-1:

After creating the Date table, let’s create another table naming as Sales.

The Sales table will look like this:

Step-2:

Go to Model view. On the Date model, click the more options(three dots) > manage relationships.

Step-3:

It will redirect to a page to create a relationship between the Dates of both the Date and Sales table.

Step-4:

Click on OK. Now a relationships mark will appear on the Model.

This is how we can create a Date table relationship on Power BI.

Power bi create date table Dax

Here we will see how to create date table in power bi using DAX. While using power bi, you will find that you need a date reference for your data to organize it by months or weeks.

If the data has some date information in it but not the specific what you need, we can build date table by using Power BI Dax, power bi can help.

• Open power bi desktop.
• Click on the modelling tab present in the ribbon. Click on new table.

• And then a new blank table will generate.
• Then give your table the first column as Date, and then use the calendar function to establish the date range, you want your table to hold.
• You can create a static date range, make sure the range you enter covers the entire period of your data and goes far enough into the future, that you will not have to adjust it any time soon.

• Expand the date table in power bi desktop, and select the column, then go to column tools in the ribbon.
• In the structure section, next to the data type you can change it to Date, as per your need.
• Then from the formatting section change, the format of your data which fits your need.

• Now, you can add columns to provide information and categories to the date range appropriate for your needs.
• To create the Year column, Go to modeling and click on the new column called Year. And write the DAX function Year = Year(‘Date'[Date]).

• Like that, you can add the new column in date table of power bi desktop, for
  • Month number= Month(‘Date'[Date])
  • Month = Format(‘Date'[Date], “MMMM”)
  • Weekday number= Weekday(‘Date'[Date],1)
  • Weekday =Format(‘Date'[Date], “DDDD”)
  • Week ending date = ‘Date'[Date]+(7-‘Date'[Weekday number])
  • Quarter = IF(‘Date'[Month Number]<4, “Qtr 1″, IF(‘Date'[Month Number]<7,”Qtr 2″, IF(‘Date'[Month Number]<10,”Qtr 3″,”Qtr 4”)))

• Now we will use the newly created date table in the power bi report independently, or we can create a relationship between the new date table and data source in the power bi desktop.
• Click on model tab -> From the ribbon click on the managing relationship in power bi desktop

• Click on the New in the Manage relationship dialog box.
• Then in Create Relation dialog box open, under the selected table and columns that are related, select a table from the drop-down in power bi desktop.
• And it will take automatically take another table
• Then Select the column From the date table and then select the date column from the data source.
• It will automatically take one to many cardinalities in power bi desktop. Click on Ok

Now you can see the relationship between two tables i.e. date table and data source table in power bi desktop.

Read: Power BI Export Data

Power bi create a date table Power query

Here we will see power bi creates a date table in the power query.

• In power bi desktop, Click on Get data -> Blank Query, It will open a blank power query editor.

• In Power Query editor in power bi, select Enter data.
• In Create Table dialog box, rename column 1 to the Start date. Then choose a date where you want to start your date range. Change the table name to the calendar. Click on Ok

• Now we will create an end date using the M language function.
• In the Power Query editor, go to Add column tab -> Click on Custom Column.
• Under the new column name, write EndDate. And under the custom column formula, write this formula: Date.From(DateTime.LocalNow()). Click on Ok.
• DateTime.LocalNow() is used to return today’s date.

• Now you can see the start date and end date in power query editor.

• Now, we will add a column to the date table for date ranges between the start date and the end date( today’s date).
• Go to add column -> Custom column. In the custom column dialog box, Under the new column name as Date. And under the custom column formula, you can write the formula:{Number.From([StartDate])..Number.From([EndDate])}
• Now you can see the date column. Then expand the date column, by clicking on expand icon you can see the numbers.

• Now we will change the data type to date, go to Transform tab -> Data Type -> Date.

• Now we will remove the Start date and end date as they have repetitive values.
• Select both the column, Select the manage column -> Remove columns

• Now we will add a column called year. Go to add column tab-> custom column. Then add give a name to the custom column as Year.
• And Write a custom formula: Date.Year([Dates])

• In the same way, you can add for a month, month name, short month name, and Quarter
  • Month = Date.Month([Dates])
  • MonthName = Date.MonthName([Dates])
  • ShortMonthName= Text.Start([MonthName],3)
  • Quarter =Date.QuarterOfYear([Dates])
• Now you can see the date table created by the power query in the power bi desktop

Read: Power bi gauge chart – How to use with examples

Power bi create a date table hierarchy

Here we will see how power bi creates a date table hierarchy. We use the date hierarchies in power bi to drill up or down on your visual and reveal additional details.

• In power bi desktop, go to the data view.
• The following hierarchy column contains day, month, Quarter, and year.
• To create a date hierarchy, I select the year column and right-click on it or click on the drop-down arrow. Select the create hierarchy.

• Now you can see the Year hierarchy having a year column. Then you can rename the hierarchy as date hierarchy.
• Select the hierarchy and right-click on it, then select rename.

• And also you can add the month column to the newly created date hierarchy.
• Right-click on the month column -> Add to hierarchy -> Date hierarchy.
• In the same way, you can add the month and Quarter.

Now you can see the date hierarchy in power bi desktop.

Read: How to change data source in Power BI

Power bi create date table with hours

Here we will see power bi create date table with hours using DAX.

• Open power bi desktop
• Go to the Data view tab in power bi desktop.
• Click on New table
• Then Write the Dax Formula i.e. Date with hour = GENERATESERIES(DATE(2016,1,1),DATE(2020,12,31),.04166666666666666666666666666667). Then click on Check icon.

Now you can see the power bi create date with hours.

Power BI create date table with hour

Now we will see how to create a date table with hours using DAX expression:

On Power BI Desktop > Modelling > New Table. It will create a new table, here we have to put this below formula.

Date with Hour = GENERATESERIES(DATE(2020,1,1),DATE(2021,12,31),.04166666666666666666666666666667)

Also, we can format the date-time formats according to our requirements, like this:

This is how to do Power BI creates a date table with an hour.

Power bi creates a date table with month and year

Here we will see how power bi creates a date table with month and year

• Open power bi desktop.
• Go to Data view, click on New table and name as a Date table
• Then write the Dax function i.e. Date = Calendar( Date(2020,1,1), Date(2020,12,31)). Then click on the check icon

• Now we will add the month and year column to the power bi date table.
• For that click on the New column, then write the Dax function to create month and year.
• The Dax function is Month and year = FORMAT(‘Date table'[Date],”MMM,yyyy”).

Read: Power BI Column Chart

Power bi create date table until today

Here we will see power bi create date table until today using Dax.

• Open power bi desktop
• Go to the Data view. And then click on New table from the ribbon.
• Then Write the Dax Function i.e. CALENDAR TABLE = CALENDAR ( “1/1/2021”, TODAY () )

Now you can see the power bi create date table until today.

Read: Power bi calculated column vs measure

Power BI date table m code

There have been many techniques for creating a data table On Power BI using DAX expression. Now we will see how to create a data table using Power Query M code. To implement this, here is the below steps:

Step-1:

First, we have to create a table and enter a start date. For this, go to Power query editor(through Transform data), then select Enter data.

For example, here we picked a start date i.e. 1/1/2021. Then click on OK.

Step-2:

Now we will create a column naming as End Date. Here we dynamically set the end date. That means it will change over time.

To do this, on Power Query Editor > Add Column > Custom column.

End Date = Date.From(DateTime.LocalNow())

Here the M code starts with “Date.From” which returns the date value from the given value. Another one is “DateTime.LocalNow()” which returns today’s day according to our local date. Make sure to change the data type to DATE in both cases i.e. Start Date and End Date.

Select Date to change the data type of End Date.

Step-3:

Now, we will add another custom column to this table, that shows the date ranges between Start Date and End Date.

{Number.From([Start Date])..Number.From([End Date])}

Here the M query starts from “Number.From”, used to convert a date into numbers so that we can get a list of the numbers and then convert the data type into the Date.

Step-4:

Now we would need to expand the “Dates” column by clicking on the below symbol:

After clicking on “Expand to New Rows”, we can see the date range in numbers.

Step-5:

Now, change the data type to Date in the Dates column. Then the data table will appear with Dates.

Step-6:

Remove both the Start Date and End Date column except Dates. Again we’ll create a custom column naming as Year to display the year of the dates. For this the M query is:

Date.Year([Dates])

Step-7:

Similarly, we will create other costume columns for Months and quarters.

For Month, the M query function is:

Date.MonthName([Dates])

For Quarter, the Mquery function is:

Date.QuarterOfYear([Dates])

Step-8:

We can see the Date table will look like this:

Step-9:

Click on Close & Apply to load the Table in Data Model. We can see and reuse the M query from Advance Editors.

This is how we can create a Date table using the M query function.

Power BI date table circular dependency

A circular dependency in DAX caused by CALENDARAUTO. We can obtain a circular dependency in two main scenarios:

• When creating a calculated column that looks perfectly justifible and the uses CALCULATE.
• When trying to create a relationship between two tables, one of which is a calculated table, or when relationship is based on a calculated column.

When we obtain a dependency list that is much wider than we would expect because of context transition, circular dependencies occur with the calculated column.

Now we will see how to handle the Circular dependency in the Power BI Date table with a step-by-step guide.

Step-1:

Click on Enter data to create a table and naming as “Employee”.

Click on Load to load the table on the Data model. Make sure to change the data type to DATE in both Join Date and Leave Date.

Step-2:

Similarly, we will create another table naming as Date, using only CALENDARAUTO( ).

Date = CALENDARAUTO()

Step-3:

Again create another table naming as Employee snapshot. For this table the code is:

Employee Snapshot = 
SELECTCOLUMNS( 
    GENERATE(
        ALLNOBLANKROW( Employee ),
        FILTER (
            ALLNOBLANKROW( 'Date'[Date] ),
            'Date'[Date] >= 'Employee'[Join Date] &&
            'Date'[Date] <= 'Employee'[Leave Date]
        )
    ),
    "Name", Employee[Name],
    "Date", 'Date'[Date]
)

Step-4:

We can see our 3 tables in the Model view.

Step-5:

Now we will create a relationship in between the Name of both Employee and Employee snapshot table.

To create a relationship on both tables, click on the More options(three dots) of the Employee table > Manage relationships. We can see a circular dependency error will appear like this:

Because of circular dependency, we can’t create relationships.

Step-6:

To avoid the Circular dependency error, we have a solution. On the Date table, replace the below code:

Date = 
VAR MaxEmployeeDate = MAX( MAX( Employee[Join Date] ), MAX( Employee[Leave Date] ) )
VAR MinEmployeeDate = MIN( MIN( Employee[Join Date] ), MIN( Employee[Leave Date] ) )
VAR MaxDate = DATE( YEAR( MaxEmployeeDate ), 12, 31 )
VAR MinDate = DATE( YEAR( MinEmployeeDate ), 1, 1 ) 
RETURN
    CALENDAR( MinDate, MaxDate )

Step-7:

With the new Date table, it’s possible to create a relationship between the Employee and Employee snapshot table like this:

This is how to handle Power BI date table circular dependency. The motive is that CALENDARAUTO is using all the dates in the Model, including those calculated tables.

How do you automatically add dates in power bi?

Here we will see about automatically add a date in power bi and also we will see the below points:

• What is auto date/time in power bi desktop?
• What is the option to enable the power bi to create a hidden auto date/time for each date column?
• How to configure auto date/time in power bi desktop?
• How does power bi Auto date/time work?
• What is the limitation for auto date/time?

What is auto date/time in power bi desktop?

• In power bi desktop the auto date/time is a data load option.
• The auto date/time purpose is to support convenient time intelligence reporting based on date columns loaded to the model.
• The auto date/time allows power bi report authors to use the data model to filter, group, and drill down by using calendar time period i.e. year, month, Qtr, date.

Enable Power bi to create a hidden auto date/time table for each date column

The power bi desktop creates the hidden auto date and time table for each date column, when the below condition are true:

1. The power bi table storage mode is import
2. The column data type in power bi is date or date/time.
3. The column in power bi is not on the “many” side of a model relationship.

How to configure auto date/time in power bi desktop?

Let’s see how to configure auto date/ time in power bi desktop. This auto date/time in power bi desktop can be enable globally or for the current file.

• In power bi desktop the global option is available for new files, and it can be turned off and on at any time.
• When you installed power bi desktop newly both( globally and current file) the options are by default on.
• The current file option, too, can be turned on or off the auto date/time at any time in power bi desktop.
• When we turned the on/off the auto date/time option, tables are created.
• Take care while turning off the auto date/time option for the current file in the power bi desktop, this will remove the auto date/time tables.
• Turning off the option in power bi desktop, break the visual, be sure you will fix them by configuring the option.

• Open your power bi desktop.
• Go to file -> options and setting -> options

• In the options dialog box, under Global -> Click on Data Load.
• Under Time intelligence, select the auto date/time for the new file.

• And also you can enable auto date/time for current file.
• In the Options dialog box, go to the current file section, click on the Data load.
• Under Time intelligence, select Auto date/time.

How does power bi Auto date/time work?

Here we will see how does power bi auto date/time work. And also we will see how we use the Auto date/time in power bi desktop

• In power bi desktop, each auto date table is in fact a calculated table, which generates rows of data by using the DAX Calendar function.
• Each date table in power bi contains six calculated columns such as Day, month number, month, Quarter, Quarter number, and year.
• And also power bi desktop creates a relationship between the auto date/time tables’ Date column and the model date column.
• In the power bi desktop, the auto date/time table contains the full calendar year encompassing all date values stored in the power bi model date column.
• Power bi when refreshes the model, each auto date/time table also refreshed.
• The date tables also define a hierarchy providing power bi visual with a drill-down through the year, Quarter, month, and day levels.

Note:

The Auto date/time table in power bi is hidden from the modelers. The power bi modeler cannot be seen in the Field pane or the power bi model view diagram.
The Auto date/ time table and its column cannot be referenced directly by DAX expression.

Now we will how to work with auto date/time table in power bi.

• In power bi, when an auto date/time table exists for a date column, that column is visible, power bi report authors won’t find that column in a fields pane.
• Instead, the power bi report author finds an expandable object that has the name of the date column.
• We can easily identify it because it is enhanced with a calendar icon.
• When power bi report authors expand the calendar object, they find a date hierarchy.
• They can see the four-level of date hierarchy: Year, Quarter, Month, Day.

• You can use the auto-generated date hierarchy to configure the visual in exactly the same way the regular hierarchies can be used.
• When auto date/ time is added to the power bi visual, power bi report authors can toggle between using the date hierarchy or date column.
• According to the requirement, you can use the date hierarchy or date column.
• Create a visual in power bi report, here I am using table visualization.
• Then add the date column in the value field.
• From the dropdown, you can select the Date column or date hierarchy.

Lastly, we will see the model calculation written in DAX by using the date column directly or the date hierarchy indirectly.

• In power bi desktop, go to modeling in power bi ribbon. Select a new column.
• Now you can see, while writing a formula a date column can be referenced in the usual way but auto date/time table columns referenced by using a special extended syntax.
• We start by referencing the date column then following it by period (.) in power bi desktop.
• Formula is Date count = COUNT(‘Date'[Date].[Date])

Read How to use Power bi maps

Limitation of auto date/time in Power bi desktop

Here we will see the what are limitation and consideration for auto date/time in power bi desktop.

1. Calendar periods only: In the date table the year and quarter column related to calendar periods only. That means the year starts with January 1 and ends with December 31.
2. Applied to all or none: When am auto date/ time is enabled in power bi desktop, it applies to all date column of import tables that are not in the many side relationship.
3. Year filtering: In the auto date/time table, the month, quarter, and day column values don’t include the year values. That means the month column contains only month names. That’s why it is important to filter(drill-down) must take place on the year column.
4. Customization: By using auto date/time, it is not possible to customize the values used to describe the time periods.
5. Single table date filtering: In power bi, it is not possible to apply a time filter to one table because each date column produces its own auto date/time table(hidden).
6. Model size: In power bi for each date column generates an auto date/time table(hidden), as result, it will increase the model size and also increase the data refresh time.
7. Other reporting tools: In power bi, it is not possible to work with auto date/time table when
   • Using paginated report Analysis service query designer in power bi
   • When connecting to the model using non-power bi report designer
   • Using the model to Analyze in Excel.

You may like the following Power BI tutorials:

• Power BI combine columns from two tables
• How to use Microsoft Power BI Scatter Chart

In this power bi tutorial, we discuss power bi creates a date table. And also we discuss the below points:

• What are the requirements to create a data table in power bi?
• What are the several techniques to add a date table to your model?
• Power bi creates date table Dax.
• How do you automatically add dates in power bi?
• Power bi creates a date table in the power query.
• Power bi creates a date table hierarchy.
• Power bi create date table with hours
• Power bi creates a date table with month and year
• Power bi create date table until today