Dax Function: Time Intelligence Functions
Category: Time Intelligence functions
Time intelligence functions in Power BI are specialized DAX functions designed to simplify time-based calculations. They enable users to perform operations such as calculating year-to-date (YTD) values, comparing periods, or aggregating data over custom time frames.
Purpose of the Function
The primary purpose of time intelligence functions is to streamline calculations that involve dates or time periods. These functions take advantage of Power BI’s built-in date and time hierarchies, allowing users to handle tasks like period-over-period comparisons, cumulative totals, and rolling averages with ease.
| Function | Description |
|---|---|
| CLOSINGBALANCEMONTH | Calculates the result of an expression at the final date of the current month’s context. |
| CLOSINGBALANCEQUARTER | Determines the value of an expression at the last date of the quarter within the current context. |
| CLOSINGBALANCEYEAR | Evaluates the expression at the final date of the current year’s context. |
| DATEADD | Produces a table with a column of dates shifted forward or backward by a specified number of intervals relative to the current context. |
| DATESBETWEEN | Creates a table with a column of dates starting from a specified beginning date and ending at a defined final date. |
| DATESINPERIOD | Returns a table containing a column of dates starting from a defined start date and continuing for a given interval type and duration. |
| DATESMTD | Provides a table with a column of dates representing the month-to-date values within the current context. |
| DATESQTD | Outputs a table with a column of dates for the quarter-to-date values in the current context. |
| DATESYTD | Generates a table with a column of dates for the year-to-date values in the current context. |
| ENDOFMONTH | Returns the last date of the month for the specified column of dates within the current context. |
| ENDOFQUARTER | Retrieves the final date of the quarter for the provided column of dates in the current context. |
| ENDOFYEAR | Produces the last date of the year for the specified column of dates in the current context. |
| FIRSTDATE | Yields the initial date in the current context for the given column of dates. |
| LASTDATE | Retrieves the most recent date in the current context for the specified column of dates. |
| NEXTDAY | Outputs a table containing a column of dates from the day following the first date in the current context. |
| NEXTMONTH | Produces a table with a column of dates for the next month, starting from the first date in the current context. |
| NEXTQUARTER | Returns a table with dates from the upcoming quarter, starting from the first date in the context. |
| NEXTYEAR | Outputs a table containing dates for the next year, starting from the first date in the current context. |
| OPENINGBALANCEMONTH | Calculates the result of an expression at the initial date of the month in the current context. |
| OPENINGBALANCEQUARTER | Evaluates the expression at the first date of the quarter within the current context. |
| OPENINGBALANCEYEAR | Determines the result of an expression at the first date of the year in the current context. |
| PARALLELPERIOD | Returns a table with a column of dates representing a period parallel to the current context but shifted forward or backward by the specified intervals. |
| PREVIOUSDAY | Produces a table with a column of dates representing the day prior to the first date in the current context. |
| PREVIOUSMONTH | Creates a table with a column of dates from the prior month, starting from the first date in the current context. |
| PREVIOUSQUARTER | Outputs a table with dates from the previous quarter, starting from the first date in the context. |
| PREVIOUSYEAR | Provides a table containing dates from the prior year based on the final date in the current context. |
| SAMEPERIODLASTYEAR | Returns a table with a column of dates shifted back by one year from the dates in the current context. |
| STARTOFMONTH | Outputs the first date of the current month for the specified column of dates. |
| STARTOFQUARTER | Produces the initial date of the quarter in the current context for the given column of dates. |
| STARTOFYEAR | Returns the first date of the year in the current context for the specified column of dates. |
| TOTALMTD | Computes the value of an expression for the month-to-date period within the current context. |
| TOTALQTD | Evaluates the expression’s value for the quarter-to-date period in the current context. |
| TOTALYTD |
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.
Time intelligence functions are DAX functions that allow you to perform calculations based on time periods, such as YTD, MTD, and rolling averages.
Yes, a properly formatted date table is required to enable time intelligence calculations in Power BI.
Yes, you can define custom fiscal year and quarter columns in your date table and use them with time intelligence functions.
They work seamlessly with date slicers and filters, automatically adjusting calculations to the selected date range.
Yes, they work with both imported data and live connections, provided a date table is available.
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
