Power BI - DAX Date Functions
DAX date functions are built-in functions that help you work with date and time values. These functions allow you to create new columns, calculate differences between dates, extract parts of a date like day, month or year and handle time-based calculations in your reports.
To understand these functions, we’ll use a sample dataset containing 50 products sold by a library supply company. The dataset includes a Date column that we'll use to demonstrate each function. To download click: Sheet1
1. Functions for Generating Dates
DAX Calendar
The CALENDAR Function generates a continuous range of dates between a specified start date and end date. This is helpful when you want to create a date table for time-based analysis.
Syntax:
CALENDAR(<start_date>, <end_date>)
DAX Date
The DATE Function returns a date based on the year, month and day you specify. It's useful for creating a date from individual year, month and day values.
Syntax:
DATE(<year>, <month>, <day>)
2. Functions for Date Manipulation
DAX Datevalue Function
The DATEVALUE Function converts a date in text format into a date value and allow Power BI to work with dates in text form.
Syntax:
DATEVALUE(date_text)
DAX Edate
The EDATE Function returns a date that is a specific number of months before or after a given start date. It’s useful for calculating future or past dates such as due dates.
Syntax:
EDATE(<start_date>, <months>)
DAX Eomonth
The EOMONTH Function returns the last day of the month before or after a specified number of months. It’s useful for calculating end-of-month dates.
Syntax:
EOMONTH(<start_date>, <months>)
3. Functions for Date Calculations
DAX Datediff Function
The DATEDIFF Function calculates the difference between two dates in a specified time unit like days, months, years.
Syntax:
DATEDIFF(<Date1>, <Date2>, <Interval>)
DAX Day Function
The DAY function extracts the day of the month from a given date. It returns a number between 1 and 31.
Syntax:
DAY(<date>)
DAX Month Function
The MONTH Function extracts the month from a given date and return a number between 1 (January) and 12 (December).
Syntax:
MONTH(<datetime>)
DAX Year Function
The YEAR Function extracts the year from a given date and return a 4-digit integer between 1900 and 9999.
Syntax:
YEAR(<date>)
4. Functions for Working with Time
DAX Now Function
Returns the current date and time. It’s used for calculating real-time information or dynamically update time-sensitive reports.
Syntax:
NOW()
DAX Today
The TODAY Function gives the current date and updates automatically every time the workbook is opened. It can also be used to calculate intervals by subtracting dates.
Syntax:
TODAY()
5. Functions for Working with Weeks
DAX Weekday
The WEEKDAY Function returns a number between 1 and 7 that represents the day of the week. By default 1 is Sunday and 7 is Saturday.
Syntax:
WEEKDAY(<date>, <return_type>)
DAX Weeknum
DAX WEEKNUM returns the week number for a given date based on two systems:
- System 1: Week 1 is the first week of the year starting on January 1.
- System 2: Week 1 is the week containing the first Thursday of the year based on the ISO 8601 standard (European week numbering).
You can choose which system to use by specifying a return type value.
Syntax:
WEEKNUM(<date>[, <return_type>])
6. Functions for Handling Time Zones
DAX Utcnow Function
Gives the current date and time in UTC. The Utcnow Function output only varies when the formula is updated. It isn't always being updated.
Syntax:
UTCNOW()
DAX UtcToday
Gives the current date in UTC.
Syntax:
UTCTODAY()
With these methods we can easily work and manipulate date datatype in Power BI.
