Dax Function: Date and Time Functions
Category: Time Intelligence functions
Date and time functions in Power BI, using DAX (Data Analysis Expressions), provide a robust set of tools to manipulate and perform calculations on date and time data. These functions allow users to extract specific components of date values, create calculated columns or measures based on time intervals, and perform complex time intelligence operations.
Purpose of the Function
The purpose of date and time functions in Power BI is to simplify operations and analyses related to time-based data. These functions enable tasks such as:
- Extracting parts of a date (e.g., year, month, day).
- Converting text to date and time formats.
- Generating calculated fields for dynamic reporting.
- Enabling time intelligence features like YTD (Year-To-Date) and MTD (Month-To-Date).
Common Use Cases:
- Create custom date hierarchies (e.g., Year-Month-Day).
- Calculate intervals, such as age or time elapsed.
- Perform rolling averages or comparisons across periods.
Function | Description |
CALENDAR | Generates a table containing a single “Date” column with a sequence of continuous dates. |
CALENDARAUTO | Produces a table with a “Date” column containing a continuous range of dates, inferred automatically. |
DATE | Outputs a specific date in the datetime format. |
DATEDIFF | Calculates the number of interval boundaries (e.g., days, months, years) between two dates. |
DATEVALUE | Converts text-formatted dates into datetime format. |
DAY | Extracts the day component from a date, returning a number between 1 and 31. |
EDATE | Provides a date that is a specified number of months before or after a given starting date. |
EOMONTH | Returns the last day of a month, in datetime format, relative to a specified number of months. |
HOUR | Retrieves the hour component from a datetime value, expressed as a number from 0 (midnight) to 23 (11 PM). |
MINUTE | Extracts the minute component from a datetime value, returning a number between 0 and 59. |
MONTH | Returns the month part of a date, expressed as a number from 1 (January) to 12 (December). |
NETWORKDAYS | Computes the total number of working days (excluding weekends and holidays) between two dates. |
NOW | Provides the current system date and time in datetime format. |
QUARTER | Identifies the quarter of a year for a given date, as a number between 1 and 4. |
SECOND | Extracts the seconds portion of a time value, returning a number from 0 to 59. |
TIME | Converts numerical values for hours, minutes, and seconds into a datetime-formatted time. |
TIMEVALUE | Converts time represented in text format into datetime format. |
TODAY | Returns the current system date, excluding the time. |
UTCNOW | Provides the current date and time in Coordinated Universal Time (UTC). |
UTCTODAY | Returns the current UTC date, excluding the time. |
WEEKDAY | Determines the day of the week for a given date, expressed as a number from 1 to 7. |
WEEKNUM | Calculates the week number of a specific date within a year, based on the specified week numbering system. |
YEAR | Extracts the year component of a date, represented as a four-digit number between 1900 and 9999. |
YEARFRAC | Computes the fractional part of a year represented by the number of days between two dates. |
Maximize the potential of Power BI and enhance your data insights with our expert consulting services. Whether you’re looking for assistance with advanced DAX functions, help designing interactive dashboards, or support in optimizing your data models for better performance, our skilled Power BI consultants are ready to provide tailored solutions for your business. Visit our Power BI consultancy page to learn more about how we can empower your organization to make more informed, data-driven decisions.
Date and time functions in Power BI are DAX expressions used to manipulate and calculate date-related data, such as extracting year, month, or performing time intelligence operations.
Yes, you can use these functions with custom calendars to align calculations with fiscal years.
DATEVALUE: Converts text to date format.TIMEVALUE: Converts text to time format.
While similar, Power BI’s DAX functions are tailored for in-memory analytics and may differ in syntax and functionality from SQL.
Power BI uses the Gregorian calendar by default unless customized with a date table.
Established in 2020, Lets Viz Technologies provides a full range of high-quality data analysis and data visualization services. We are also an authorized Zoho Partner.
Sitelinks
