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 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

Why create a data table in power bi?

Here you will see, what are the requirements to create a date table in power bi. To work with the DAX time intelligence function the model requirement is, You must have at least one date table in the power bi model.

The requirements to create data table in power bi are

  • The date 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

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 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.
Power bi creates date table Dax
Power bi creates date table Dax
  • 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.
Power bi creates date table Dax
Power bi creates date table Dax
  • 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.
Power bi create a date table
Power bi create a date table
  • 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]).
Microsoft Power bi creates date table Dax
Microsoft Power bi creates date table Dax
  • 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
Microsoft Power bi creates date table Dax
Microsoft Power bi creates date table Dax
  • 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
Microsoft Power bi creates date table Dax
Microsoft Power bi creates date table Dax

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

Microsoft Power bi creates date table Dax
Microsoft Power bi creates date table Dax

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.
Power bi creates a date table in the power query
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
Power bi creates a date table in the power query
Power bi creates a date table in the power query
  • 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.
Power bi creates a date table in the power query
Custom column in power query editor
  • Now you can see the start date and end date in power query editor.
Power bi creates a date table in the power query
Microsoft Power bi creates a date table in the power query
  • 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.
Power bi creates a date table in the power query
How Power bi creates a date table in the power query
  • Now we will change the data type to date, go to Transform tab -> Data Type -> Date.
transform data type
  • 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
Power bi creates a date table in the power query
How Power bi creates a date table in the power query
  • 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])
how Power bi creates a date table in the power query
Custom column in power query
  • 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
microsoft  Power bi creates a date table in the power query
microsoft Power bi creates a date table in the power query

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.
power bi creates a date table hierarchy
Create date 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.
power bi creates a date table hierarchy
rename the date hirearchy in power bi
  • 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.
power bi creates a date table hierarchy
power bi creates a date table hierarchy

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

Microsoft power bi creates a date table hierarchy
Microsoft power bi creates a date table hierarchy

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.
Power bi create date table with hours
Power bi create date table with hours

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

Microsoft Power bi create date table with hours
Microsoft Power bi create date table with hours

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
Power bi creates a date table with month and year
Power bi creates a date table with month and year
  • 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”).
Power bi creates a date table with month and year
Power bi creates a date table with month and year

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 () )
Power bi create date table until today
Power bi create date table until today

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

Microsoft Power bi create date table until today
Microsoft Power bi create date table until today

Read: Power bi calculated column vs measure

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
How do you automatically add dates in power bi?
How do you automatically add dates in power bi?
  • In the options dialog box, under Global -> Click on Data Load.
  • Under Time intelligence, select the auto date/time for the new file.
How to configure auto date/time in power bi desktop?
How to configure auto date/time in power bi desktop?
  • 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 to configure auto date/time in power bi desktop?
How to configure auto date/time in power bi desktop?

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.
How does power bi Auto date/time work
How does power bi Auto date/time work
  • 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.
How does power bi Auto date/time work
How does power bi Auto date/time work

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])
How does power bi Auto date/time work
How does power bi Auto date/time work

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:

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
>