The Date Table
I N T R O D U C T I O N TO DA X I N P OW E R B I
Jess Ahmet
Content Developer, Datacamp
Working with dates
Example Date: 2020/09/20 12:52 Format Function
Date and Time Functions Weekday: FORMAT(<date>, <"dddd">) >
Friday
YEAR(<date>) > 2020
Time: FORMAT(<date>, <"h:nn:ss">) >
QUARTER(<datetime>) > 3
"12:52:00"
MONTH(<datetime>) > 9
Time Intelligence Functions
LASTDATE()
DATESBETWEEN()
DATEADD()
1 h ps://docs.microso .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 pa erns 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
Di cult 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.
Bene ts:
Filter by multiple date a ributes such as Year and Month
Custom calendar view/de nitions such as scal 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 speci ed 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 speci ed 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!
I N T R O D U C T I O N TO DA X I N P OW E R B I
Dates and Quick
Measures
I N T R O D U C T I O N TO DA X I N P OW E R B I
Jess Ahmet
Content Developer at DataCamp
Let's practice!
I N T R O D U C T I O N TO DA X I N P OW E R B I
Congratulations!
I N T R O D U C T I O N TO DA X I N P OW E R B I
Jess Ahmet
Content Developer at DataCamp
Course Recap
Chapter 1 Chapter 2
DAX functions Row vs. lter context
Creating calculated columns Use of variables with VAR
Creating measures Working with iterator functions
Using CALCULATE() to lter measures
Chapter 3
Creating calculated tables
Date and Time, Format and Time
Intelligence Functions
Quick Measures
INTRODUCTION TO DAX IN POWER BI
Practice makes perfect!
INTRODUCTION TO DAX IN POWER BI
Become a DAX
master!
I N T R O D U C T I O N TO DA X I N P OW E R B I
