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.
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.
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
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.
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.
- Creating DimDate in Power BI – DimDate Series (1/3)
- Working with TIME Functions – INTELLIGENCE – DimDate Series (2/3)
- Display your dates in Power BI – DimDate Series (3/3)
If you want us to help your business or company contact us at firstname.lastname@example.org or call us at +34 962 681 242
Business Intelligence Consultant. Especializado en creación de Data Warehouse, Analysis Services, Power BI, SSIS y Reporting Services.