When using time intelligence functions, create a separate calendar date table in Power BI desktop or powerpivot to ensure the formulas work as intended. Parameters in square brackets are optional.
Often the expression used in the examples is "sales". If the definitions are not clear, you can try them yourself with Power BI using the formulas, examples and dataset provided.
| Name of function | Description |
|---|---|
| CLOSINGBALANCEMONTH(expression , dates ,[filter]) | Evaluates specified expression for the last day of the given month. |
| CLOSINGBALANCEQUARTER(expression , dates ,[filter]) | Evaluates specified expression for the last day of the given quarter. |
| CLOSINGBALANCEYEAR(expression , dates , [filter]) | Evaluates specified expression for the last day of the given year. |
| DATEADD(date , number_of_intervals to shift , interval) | Used for shifting (offsetting) a set of dates. Intervals can be specifed as day, month, quarter or year. |
| DATESBETWEEN(column , start_date , end_date) | Returns a table of dates. Create as a new table, or use as a filter in a CALCULATE expression. |
| DATESINPERIOD(date ,start_date ,number_of_intervals , intervals) | Returns a table of dates. Create as a new table, or use as a filter in a CALCULATE expression. Intervals can be specifed as day, month, quarter or year. |
| DATESMTD(date) | Use with CALCULATE to return month to date sales. This will "reset" each month. |
| DATESQTD (date) | Use with CALCULATE to return quarter to date sales. This will "reset" each quarter. |
| DATESYTD (date [Year_end_date]) | Use with CALCULATE to return year to date sales. This will "reset" each year. |
| ENDOFMONTH(date) | Returns the last day of the month |
| ENDOFQUARTER(date) | Returns the last day of the quarter |
| ENDOFYEAR(date) | Returns the last day of the year |
| FIRSTDATE (datecolumn) | Use to return the first date in your table. |
| FIRSTNONBLANKDATE (datecolumn , [expression]) | Returns the first value in the column, where the expression is not blank. |
| LASTDATE (datecolumn) | Use to return the last date in your table. |
| LASTNONBLANKDATE (datecolumn ,[expression]) | Returns the last value in the column, where the expression is not blank. |
| NEXTDAY(date) | Use with CALCULATE to return next day sales ( relative to the dates you are using ) |
| NEXTMONTH(date) | Use with CALCULATE to return next month sales ( relative to the dates you are using ) |
| NEXTQUARTER (date) | Use with CALCULATE to return next quarter sales ( relative to the dates you are using ) |
| NEXTYEAR(date[,YE_date]) | Use with CALCULATE to return next year sales ( relative to the dates you are using ) |
| OPENINGBALANCEMONTH(expression, dates , [filter]) | Evaluates an expression at the first date of a month. |
| OPENINGBALANCEQUARTER(expression, dates , [filter]) | Evaluates an expression at the first date of a quarter. |
| OPENINGBALANCEYEAR(expression , dates , [[filter]]) | Evaluates an expression for the first date of a year. |
| PARALLELPERIOD(date) | Returns a single column table of dates which are parallel to the date column shifted by specified number of intervals forward or backward. |
| PREVIOUSDAY(date) | Use with CALCULATE to return previous day sales |
| PREVIOUSMONTH(date) | Use with CALCULATE to return previous month sales |
| PREVIOUSQUARTER(date) | Use with CALCULATE to return previous quarter sales |
| PREVIOUSYEAR(date) | Use with CALCULATE to return previous year sales |
| SAMEPERIODLASTYEAR(date) | Use with CALCULATE to return sales from last year. |
| STARTOFMONTH (date) | Returns the first day of the month |
| STARTOFQUARTER (date) | Returns the first day of the quarter |
| STARTOFYEAR(date,[YEAR]) | Returns the first day of the year |
| TotalMTD(expression,dates,[filter]) | Use with CALCULATE to return month to date sales. (i.e see DATESMTD) |
| TotalQTD(expression,dates,[filter]) | Use with CALCULATE to return quarter to date sales. (i.e see DATESQTD) |
| TotalYTD(expression,dates,[filter]) | Use with CALCULATE to return year to date sales. (i.e see DATESYTD) |