All our data models have a common dimension, the dimension of date or DimDate. Thanks to the date dimension we can make comparisons of data between ranges of dates, see how the annual production of a company has gone with a quick glance or any functionality that occurs to us whenever there is a relationship between the date dimension and the fact table.

Table Creation

As soon as we have Power Bi Desktop opened with our loaded fact table, in our case Disney Movies, we can start to create the date dimension. To do this, we place ourselves in the Data part and click on New Table.

DimDate dimensiontiempo fecha fechas date aleson itc microsoft base de datos sql server mysql oracle postgresql powerbi power bi business intelligence azure ssis ssas ssrs Azure SQL Database datawarehouse stretch databases managed instance elastic pool data factory

Writing the following code in the creation bar, we will create a table with all the dates that fall between 2016-01-01 and 2019-12-31.

If we wanted the date range to be dynamic, we should simply modify the CALENDAR part. With the following formula we would take from January 1 from two years ago until December 31, within two years.

Once the main columns have been loaded, we can start to create the columns that we will use in our Power Bi visualizations with their respective sorting columns. The sort columns are mandatory since a disordered graphic has no value.

Disordered Graphic vs Order Graphic

DimDate dimensiontiempo fecha fechas date aleson itc microsoft base de datos sql server mysql oracle postgresql powerbi power bi business intelligence azure ssis ssas ssrs Azure SQL Database datawarehouse stretch databases managed instance elastic pool data factory

Column Creation

The basic columns that should not be missing in any date dimension are the following:
  • Year= YEAR ( [Date] )
  • MonthNumber= FORMAT ( [Date]; “MM” )
  • MonthNameShort= FORMAT ( [Date]; “mmm” )
  • MonthNameLong= FORMAT ( [Date]; “mmmm” )
  • DayOfWeekNumber= WEEKDAY ( [Date] )
  • DayOfWeek= FORMAT ( [Date]; “dddd” )
  • DayOfWeekShort= FORMAT ( [Date]; “ddd” )
  • Quarter“= “Q” & FORMAT ( [Date]; “Q” )
  • YearQuarter= FORMAT ( [Date]; “YYYY” ) & “/Q” & FORMAT ( [Date]; “Q” )

To create the new columns, click on New Column and insert the formula in the bar. We can create as many columns as we want and customize them to our liking, Power Bi has a wide range of date formats.DimDate dimensiontiempo fecha fechas date aleson itc microsoft base de datos sql server mysql oracle postgresql powerbi power bi business intelligence azure ssis ssas ssrs Azure SQL Database datawarehouse stretch databases managed instance elastic pool data factory

When the columns were created we would have to sort the columns by their order column. To create the sort, we select the column that we want to sort with respect to another column and click on Sort by Column and select the sort column.

DimDate dimensiontiempo fecha fechas date aleson itc microsoft base de datos sql server mysql oracle postgresql powerbi power bi business intelligence azure ssis ssas ssrs Azure SQL Database datawarehouse stretch databases managed instance elastic pool data factory

Table Relationship

One of the options that we must not neglect is Mark as Date Table, this option indicates that it is the date table of the model and internally makes the following validations:

  • They contain unique values
  • They contain values that are not null
  • They contain contiguous date values (from start to finish)
  • They have the same timestamp on each value (if they are of the Date / Time)

DimDate dimensiontiempo fecha fechas date aleson itc microsoft base de datos sql server mysql oracle postgresql powerbi power bi business intelligence azure ssis ssas ssrs Azure SQL Database datawarehouse stretch databases managed instance elastic pool data factory

To finish we only need to relate the date dimension with the fact table.

DimDate dimensiontiempo fecha fechas date aleson itc microsoft base de datos sql server mysql oracle postgresql powerbi power bi business intelligence azure ssis ssas ssrs Azure SQL Database datawarehouse stretch databases managed instance elastic pool data factory

And check that the relationship works correctly.

In this page of Power Bi we can visualize the production of Disney per year and the films with greater production.

Here I leave the creation script of the date dimension of the example, so that you start from a base to create your own date dimensions.

In the next post of the series we will talk about time measures and how to do them quickly in DAX.

DimDate Series:

  1. Creating DimDate in Power BI (1/3)
  2. Creating DimDate in Power BI (2/3)

If you want us to help your business or company visit our Data Analytics Page.

Recommended Posts

Leave a Comment

This site uses Akismet to reduce spam. Learn how your comment data is processed.