Power BI - DAX Date Functions

Knowing when and how to use DAX functions helps you build strong, high-performance data models and analysis large amounts of data in Power BI. In this article, we’ll explore important DAX Date Functions to help you manage dates in Power BI

To begin load a dataset that contains date fields. Here we are taking the records of 50 products sold by a library supply company. Dataset Used: 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>)

5. 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()

6. 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:

1. System 1: Week 1 is the first week of the year starting on January 1.
2. 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>])

7. Functions for Handling Time Zones

DAX Utcnow Function

Gives the current date and time in UTC. The UTCNOW function's 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.