The Date table

Introduction to DAX in Power BI

Jess Ahmet

Content Developer, DataCamp

Working with dates

Example Date: 2020/09/20 12:52

Date and Time Functions

  • YEAR(<date>) > 2020
  • QUARTER(<datetime>) > 3
  • MONTH(<datetime>) > 9

Format Function

  • Weekday: FORMAT(<date>, <"dddd">) > Friday
  • Time: FORMAT(<date>, <"h:nn:ss">) > "12:52:00"

Time Intelligence Functions

  • LASTDATE()
  • DATESBETWEEN()
  • DATEADD()
1 https://docs.microsoft.com/en-us/dax/format-function-dax
Introduction to DAX in Power BI

Working with dates

  • Evaluate data in time-series to spot trends and patterns i.e seasonal performance
  • Out of the box features:
    • 20+ Date and Time Functions
    • 30+ Time Intelligence Functions
    • Automatically enabled date hierarchies
      • Drill-able to year, quarter, month and day
Introduction to DAX in Power BI

The importance of a date table

Issues of relying on only dates from transactional tables:

  • Gaps in dates i.e no sales made on 20th September
  • Returns wrong results when using time-intelligence functions
    • No error, wrong result
    • Difficult to troubleshoot
Introduction to DAX in Power BI

Creating a Date Table

  • A dedicated date table is highly recommended for accurate reporting using time-intelligence functions.

Benefits:

  • Filter by multiple date attributes such as Year and Month
  • Custom calendar view/definitions such as fiscal dates
  • Use of time-intelligence features to select a time horizon (e.g Today, Yesterday, Last 30 days)

Types of Analysis:

  • Revenue by Day of Week, Fiscal Performance, Public Holidays
Introduction to DAX in Power BI

Creating a Date table

CALENDAR()

  • Syntax: CALENDAR(<start_date>, <end_date>)
  • Returns a table with a single column 'date' that contains a continuous set of dates inclusive of the specified dates
  • Example: CALENDAR('2020-01-01', '2020-12-31')
Introduction to DAX in Power BI

Creating a Date table

CALENDAR()

  • Syntax: CALENDAR(<start_date>, <end_date>)
  • Returns a table with a single column 'date' that contains a continuous set of dates inclusive of the specified dates
  • Example: CALENDAR('2020-01-01', '2020-12-31')
Date
2020-01-01
2020-01-02
...
2020-12-31
Introduction to DAX in Power BI

Creating a Date table

CALENDARAUTO()

  • Syntax: CALENDARAUTO(<fiscal_year_end_month>)
  • Returns a table with a single column 'date' that automatically takes the earliest and latest date in the model and internally calls CALENDAR().
  • Example: CALENDARAUTO(12)
Introduction to DAX in Power BI

Creating a Date table

CALENDARAUTO()

  • Syntax: CALENDARAUTO(<fiscal_year_end_month>)
  • Returns a table with a single column 'date' that automatically takes the earliest and latest date in the model and internally calls CALENDAR().
  • Example: CALENDARAUTO(12)
Date
2020-01-01
2020-07-31
...
2020-12-31
Introduction to DAX in Power BI

Let's practice!

Introduction to DAX in Power BI

Preparing Video For Download...