POWER BI: Features

A List of Time Intelligence Functions

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)





Services
  • SEO
  • Online Advertising
  • Online Ordering For Restaurants
  • Data Analytics

  • External Resources 4 Developers
  • CSS Tricks
  • MDN

  • Dash-Intel.com is a Power BI and Tableau resource site around data visualizations and building business dashboards.

    Data Analytics consultants in Edmonton, Alberta specializing in Tableau and marketing analytics

    Copyright 2020 Dash-Intel.com Terms / All rights reserved.