TIME INTELLIGENCE FUNCTIONS (PowerPivot

May 18

Time Intelligence Functions (DAX)

Hi there,

In this post, we will discuss about one of the types of DAX Functions. These functions basically supports the needs of Business Intelligence Analysis. These functions will help to manipulate data using time periods.

TYPES OF DAX:

DATE AND TIME FUNCTIONS
FILTER FUNCTIONS
INFORMATION FUNCTIONS
LOGICAL FUNCTIONS
MATHEMATICAL AND TRIGNOMETRIC FUNCTIONS
STATISTICAL FUNCTIONS
TEXT FUNCTIONS
TIME INTELLIGENCE FUNCTIONS

TIME INTELLIGENCE FUNCTIONS:

...

CLOSINGBALANCEMONTH: This function evaluates the last date of the month.
CLOSINGBALANCEQUARTER: This function evaluates the last date of the quarter.
CLOSINGBALANCEYEAR: This function evaluates the last date of the year.
DATEADD: This function returns the number of intervals the columns of dates shifted either forward or backword.
DATESBETWEEN: This function returns the start date and the end date
DATESINPERIOD: This function returns the specified number of intervals from start date.
DATESMTD: This function returns a table that contains the column of dates for the Month-to-Date.
DATESQTD: This function returns a table that contains a column of dates for Quarter-to-Date.
DATESYTD: This function returns a table that contains a column of dates for Year-to-Date
ENDOFMONTH: This function returns the last date of the month.
ENDOFQUARTER: This function returns the last date of the quarter.
ENDOFYEAR: This function returns the last date of the year.
FIRSTDATE: This function returns the first date in the column of dates.
FIRSTNONBLANK: This function retunrs the first value in a column, filtered, by non-blank cells.
LASTDATE: This function returns the last date in the column of dates.
LASTNONBLANK: This function retunrs the last value in a column, filtered, by non-blank cells.
NEXTDAY: This function returns a table that contains a column of dates from the next day, based on the specified date.
NEXTMONTH: This function returns a table that contains a column of dates from next one month, based on the specified date.
NEXTQUARTER: This function returns a table that contains a column of dates from next one quarter.
NEXTYEAR: This function returns a table that contains a column of dates from next one year.
OPENINGBALANCEMONTH: This function evaluates the expression at first date of the month.
OPENINGBALANCEQUARTER: This function evaluates the expression at first date of the quarter.
OPENINGBALANCEYEAR: This function evaluates the expression at the first date of the year.
PARALLELPERIOD: This function returns a table that contains a column of dates that represent a period parallel to the dates.
PREVIOUSDAY: This function returns a table that contains all dates representing the day that is previous to the first date.
PREVIOUSMONTH: This function returns a table that contains all the dates for the previous month, based on the first date in the column.
PREVIOUSQUARTER: This function returns a table that contains all the dates for the previous quarter, based on the first date in the column
PREVIOUSYEAR: This function returns a table that contains all the dates for the previous year, based on the first date in the column.
SAMEPERIODLASTYEAR: This function returns a table that contains all the dates for the same period last year, based on the first date in the column.
STARTOFMONTH: This function returns first date of the month in a given table contains column of dates.
STARTOFQUARTER: This function returns first date of the quarter in a given table contains column of dates.
STARTOFYEAR: This function returns first date of the year in a given table contains column of dates.
TOTALMTD: This function evaluates the value of an expression for the month to date.
TOTALQTD: This function evaluates the value of an expression for the quarter to date.
TOTALYTD: This function evaluates the value of an expression for the year to date.