How to create Date tables in Power BI. Why are they remarkably important in data models? | CloudFronts

How to create Date tables in Power BI. Why are they remarkably important in data models?

Posted On February 13, 2020 by Sandip Patel Posted in

In this blog I am going to explain you how to create a Date table in Power BI desktop. A date table is a table that contains one column of DateTime type with unique values.

  1. It’s also called CALENDAR table
  2. A data model typically contains a date table
  3. A date table is designed to slice dates
  4. It should contain all dates included in the period to analyse
  5. It should contain one column of DateTime type
  6. Each day should be represented by a single a row
  7. There should be no gaps in the data
  8. It should be marked as a date table
  9. Using a date table with only the relevant year is best practise
  10. It’s recommended to have a rich set of additional columns
  11. Give the data model the flexibility to perform time intelligence calculations by using specific DAX functions
  12. Slice data by using one of its columns
  13. Create relationships between a date table and other tables in the data model
  14. Filter and group data across multiple tables

Types of Date tables

There are two ways to create a table

  1. Automatic Date Tables

Power BI automatically creates a date table for each Date or DateTime column in the model. This makes it possible to slice each date by year, quarter, month and day.

  1. Calculated Date Tables

It can be done by using two DAX functions: CALENDAR and CALENDARAUTO

Syntax:

Dates = CALENDAR(<StartDate>,<EndDate>)

Dates = CALENDAR

(      FIRSTDATE(Table[column]),    LASTDATE(Table[column])        )

Calendar functions return a table with a single column named “Date” and a list of values for the dates. Calendar requires the boundaries of the set of dates.

CalendarAuto searches among all the dates in the data model and automatically finds the first and last year referenced within the model.

Dates = CALENDARAUTO()

The final formula for the calculated table should look something like this:

Date =

ADDCOLUMNS (

CALENDAR (DATE(2018,1,1), DATE(2025,12,31)),

“DateAsInteger”, FORMAT ([Date], “YYYYMMDD”),

“Year”, YEAR ([Date]),

“Monthnumber”, FORMAT ([Date], “MM”),

“YearMonthnumber”, FORMAT ([Date], “YYYY/MM”),

“YearMonthShort”, FORMAT ([Date], “YYYY/mmm”),

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

)

Once the date table is in place, it is a good idea to mark it as a date table as shown here:

This both simplifies the code to author time intelligence calculations and automatically disables the auto Date/Time feature.

I hope this will help you.

 


Share Story :

By continuing to use the site, you agree to the use of cookies. more information

The cookie settings on this website are set to "allow cookies" to give you the best browsing experience possible. If you continue to use this website without changing your cookie settings or you click "Accept" below then you are consenting to this.

Close