Skip to content
Category: Data Analytics
2019-01-15

Creating DimDate in Power BI – DimDate Series (1/3)

Creating DimDates with Power BI is the perfect ally for your company’s data analytics. 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.Transact-SQL

DimDate = 
ADDCOLUMNS (
CALENDAR ("2016-01-01"; "2019-12-31");
"DateInt"; FORMAT ( [Date]; "YYYYMMDD" ))

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.

CALENDAR (DATE(YEAR(TODAY())-2;1;1); DATE(year(TODAY())+2;12;31));

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” )
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 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.

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

If you are interested in the Business Intelligence World, don’t hesitate to visit our Data Analytics Page

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.

DimDate = 
ADDCOLUMNS (
CALENDAR ("2000-01-01"; "2019-12-31");
"DateInt"; FORMAT ( [Date]; "YYYYMMDD" );
"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" )
)

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 – DimDate Series (1/3)
  2. Working with TIME Functions – INTELLIGENCE – DimDate Series (2/3)
  3. Display your dates in Power BI – DimDate Series (3/3)

If you want us to help your business or company contact us at info@aleson-itc.com or call us at +34 962 681 242

Complete este formulario para recibir la guía de Windows Server en Azure
*Obligatorio
Complete este formulario para recibir la guía de Windows Server en Azure
Gracias por rellenar el formulario [name]. ¡Aquí tienes tu eBook Gratis!
Complete este formulario para recibir 4 best practices to implement a comprehensive Zero Trust security approach
*Obligatorio
Complete este formulario para recibir 4 best practices to implement a comprehensive Zero Trust security approach
Gracias por rellenar el formulario [name]. ¡Aquí tienes tu eBook Gratis!
Complete este formulario para recibir Cloud Migration Essentials
*Obligatorio
Complete este formulario para recibir Cloud Migration Essentials
Gracias por rellenar el formulario [name]. ¡Aquí tienes tu eBook Gratis!
Complete este formulario para recibir Cloud security Advice for Nonprofit Leaders
*Obligatorio
Complete este formulario para recibir Cloud security Advice for Nonprofit Leaders
Gracias por rellenar el formulario [name]. ¡Aquí tienes tu eBook Gratis!
Complete este formulario para recibir Prevent data leaks with Microsoft 365 Business Premium
*Obligatorio
Complete este formulario para recibir Prevent data leaks with Microsoft 365 Business Premium
Gracias por rellenar el formulario [name]. ¡Aquí tienes tu eBook Gratis!
Complete this form to recieve the guide of Windows Server on Azure
*Required
Complete this form to recieve the guide of Windows Server on Azure
Thank you for filling out the form [name]. Here's your Free eBook!
Complete this form to recieve 4 best practices to implement a comprehensive Zero Trust security approach
*Required
Complete this form to recieve 4 best practices to implement a comprehensive Zero Trust security approach
Thank you for filling out the form [name]. Here's your Free eBook!
Complete this form to recieve Cloud Migration Essentials
*Required
Complete this form to recieve Cloud Migration Essentials
Thank you for filling out the form [name]. Here's your Free eBook!
Complete this form to recieve Cloud security Advice for Nonprofit Leaders
*Required
Complete este formulario para recibir Cloud security Advice for Nonprofit Leaders
Thank you for filling out the form [name]. Here's your Free eBook!
Complete this form to recieve Prevent data leaks with Microsoft 365 Business Premium
*Obligatorio
Complete this form to recieve Prevent data leaks with Microsoft 365 Business Premium
Thank you for filling out the form [name]. Here's your Free eBook!
Complete this form to recieve Cloud Migration Simplified Ebook.
*Obligatorio
Complete this form to recieve Prevent data leaks with Microsoft 365 Business Premium
Thank you for filling out the form [name]. Here's your Free eBook!