Time-Intelligence Functions

Data Analysis Expressions (DAX) includes time intelligence functions to support the needs of Business Intelligence analysis by enabling you to manipulate data using time periods, including days, months, quarters, and years, and then build and compare calculations over those periods.

In this category

FunctionDescription
CLOSINGBALANCEMONTHEvaluates the expression at the last date of the month in the current context.
CLOSINGBALANCEQUARTEREvaluates the expression at the last date of the quarter in the current context.
CLOSINGBALANCEYEAREvaluates the expression at the last date of the year in the current context.
DATEADDReturns a table that contains a column of dates, shifted either forward or backward in time by the specified number of intervals from the dates in the current context.
DATESBETWEENReturns a table that contains a column of dates that begins with the start_date and continues until the end_date.
DATESINPERIODReturns a table that contains a column of dates that begins with the start_date and continues for the specified number_of_intervals.
DATESMTDReturns a table that contains a column of the dates for the month to date, in the current context.
DATESQTDReturns a table that contains a column of the dates for the quarter to date, in the current context.
DATESYTDReturns a table that contains a column of the dates for the year to date, in the current context.
ENDOFMONTHReturns the last date of the month in the current context for the specified column of dates.
ENDOFQUARTERReturns the last date of the quarter in the current context for the specified column of dates.
ENDOFYEARReturns the last date of the year in the current context for the specified column of dates.
FIRSTDATEReturns the first date in the current context for the specified column of dates.
FIRSTNONBLANKReturns the first value in the column, column, filtered by the current context, where the expression is not blank
LASTDATEReturns the last date in the current context for the specified column of dates.
LASTNONBLANKReturns the last value in the column, column, filtered by the current context, where the expression is not blank.
NEXTDAYReturns a table that contains a column of all dates from the next day, based on the first date specified in the dates column in the current context.
NEXTMONTHReturns a table that contains a column of all dates from the next month, based on the first date in the dates column in the current context.
NEXTQUARTERReturns a table that contains a column of all dates in the next quarter, based on the first date specified in the dates column, in the current context.
NEXTYEARReturns a table that contains a column of all dates in the next year, based on the first date in the dates column, in the current context.
OPENINGBALANCEMONTHEvaluates the expression at the first date of the month in the current context.
OPENINGBALANCEQUARTEREvaluates the expression at the first date of the quarter, in the current context.
OPENINGBALANCEYEAREvaluates the expression at the first date of the year in the current context.
PARALLELPERIODReturns a table that contains a column of dates that represents a period parallel to the dates in the specified dates column, in the current context, with the dates shifted a number of intervals either forward in time or back in time.
PREVIOUSDAYReturns a table that contains a column of all dates representing the day that is previous to the first date in the dates column, in the current context.
PREVIOUSMONTHReturns a table that contains a column of all dates from the previous month, based on the first date in the dates column, in the current context.
PREVIOUSQUARTERReturns a table that contains a column of all dates from the previous quarter, based on the first date in the dates column, in the current context.
PREVIOUSYEARReturns a table that contains a column of all dates from the previous year, given the last date in the dates column, in the current context.
SAMEPERIODLASTYEARReturns a table that contains a column of dates shifted one year back in time from the dates in the specified dates column, in the current context.
STARTOFMONTHReturns the first date of the month in the current context for the specified column of dates.
STARTOFQUARTERReturns the first date of the quarter in the current context for the specified column of dates.
STARTOFYEARReturns the first date of the year in the current context for the specified column of dates.
TOTALMTDEvaluates the value of the expression for the month to date, in the current context.
TOTALQTDEvaluates the value of the expression for the dates in the quarter to date, in the current context.
TOTALYTDEvaluates the year-to-date value of the expression in the current context.

***** Related Blog Posts *****
60 Page DAX Formula Reference Guide Download Now Available
Show Only Top Or Bottom Results Using Ranking Formula Techniques – Power BI & DAX

Time Intelligence In Power BI: How to Calculate The Number of Transactions Made in the Last N Days
Time Intelligence For Non Standard Date Tables In Power BI

***** Related Course Modules *****
Ultimate Beginners Guide to DAX
Mastering DAX Calculations
Solving Analytical Scenarios w/ Power BI and DAX
DAX Formula Deep Dives
Advanced DAX Combinations

***** Related Support Forum Posts*****
Mastering DAX Calculations: What Is DAX?
Understanding DAX Formula
Developing Complex Dax functions
Difference Between Rolling Originations 90D and Rolling Originations 90D LY
Show Results Up To Current Date Or A Specific Date In Power BI

Center of Excellence

DOWNLOAD


Download our comprehensive DAX Formula Reference Guide as perfect companion as you learn how to use DAX formulas within Power BI.


Download DAX Formulas Reference Guide
Download DAX Formula Reference Guide
Membership