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

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.

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.

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

Know More

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)

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

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.