Function 
Description 
CALENDAR (start date, end date) 
New in Excel 2016/Power BI Desktop.Returns a table with a single column named “Date” that contains a

CALENDARAUTO ([end month of fiscal year]) 
New in Excel 2016/Power BI Desktop.Returns a table with a single column named “Date” that contains a

DATE (year, month, day) 
Returns the specified date in datetime format. 
DATEDIFF (start date, end date, interval) 
New in Excel 2016/Power BI Desktop.Returns the count of interval boundaries crossed between two dates. 
DATEVALUE (date text) 
Returns the specified date in datetime format. 
DAY (date) 
Returns the day of the month, a number from 1 to 31. 
EDATE (start date, months) 
Returns a date in datetime format that is a number of months after the

EOMONTH (start date, months) 
Returns a date in datetime format that is the last date in the month

HOUR (datetime value) 
Returns the hour as a number from 0 (12:00 A.M.) to 23 (11:00 P.M.). 
MINUTE (datetime value) 
Returns the minute as a number from 0 to 59, given a date and time

MONTH (datetime value) 
Returns the month as a number from 1 (January) to 12 (December). 
NOW () 
Returns a date in datetime format. 
SECOND (datetime value) 
Returns the seconds of a time value, as a number from 0 to 59. 
TIME (hour, minute, second) 
Converts hours, minutes, and seconds given as numbers to a time in

TIMEVALUE (time text) 
Converts a time in text format to a time in datetime format. 
TODAY () 
Returns the current date in datetime format. 
Function 
Notes 
WEEKDAY (date, return type) 
Returns a number from 1 to 7 identifying the day of the week of a date.

WEEKNUM (date, return type) 
Returns the week number for the given date and year according to the

YEAR (datetime value) 
Returns the year of a date as a fourdigit integer in the range

YEARFRAC (start_date,

Calculates the fraction of the year represented by the number of whole


Description 

CLOSINGBALANCEMONTH(expression, dates, [filter])

Evaluates the expression at the last date


CLOSINGBALANCEQUARTER(expression, dates, [filter])

Evaluates the expression at the last date


CLOSINGBALANCEYEAR(expression,

Evaluates the expression at the last date


DATEADD

Returns a table that contains a column of dates, shifted either forward


DATESBETWEEN (dates, start_date, end_date)

Returns a table that contains a column of dates that begins


DATESINPERIOD (dates, start_date, number_of_intervals, interval)

Returns a table that contains a column of dates that begins


DATESMTD (dates) 
Returns a table that contains a column of the dates for the


DATESQTD (dates)

Returns a table that contains a column of the dates for the


DATESYTD (dates, [year_end_date])

Returns a table that contains a column of the dates for the


ENDOFMONTH (dates)

Returns the last date of the


ENDOFQUARTER (dates)

Returns the last date of the quarter in the current context


ENDOFYEAR (dates, [year_end_date])

Returns the last date of the year in the current context for


FIRSTDATE (dates)

Returns the first


FIRSTNONBLANK (column, expression)

Returns the first


LASTDATE (dates)

Returns the last date


LASTNONBLANK (column, expression)

Returns the last


NEXTDAY (dates)

Returns a table that


NEXTMONTH (dates)

Returns a table that


NEXTQUARTER (dates)

Returns a table that


NEXTYEAR (dates, [year_end_date]) 
Returns a table that


OPENINGBALANCEMONTH(expression, dates, [filter])

Evaluates the expression at the first date of the month in


OPENINGBALANCEQUARTER(expression, dates, [filter])

Evaluates the expression at the first date of the quarter in


OPENINGBALANCEYEAR

Evaluates the expression at the first date of the year in



Returns a table that contains a column of dates that represents a period


shifted a number of intervals either forward


PREVIOUSDAY (dates)

Returns a table that contains a column of all dates representing the day


PREVIOUSMONTH (dates)

Returns a table that contains a column of all dates from the previous


PREVIOUSQUARTER (dates)

Returns a table that contains a column of


PREVIOUSYEAR (dates,[year_end_date]) 
Returns a table that contains a column of all dates from the previous


SAMEPERIODLASTYEAR (dates)

Returns a table that contains a column of


STARTOFMONTH (dates)

Returns the first date of the month in the current context for the


STARTOFQUARTER (dates)

Returns the first date of the quarter in the


STARTOFYEAR (dates)

Returns the first date of the year in the current context for the


TOTALMTD (expression, dates, [filter])

Evaluates the value of the expression for the month to


TOTALQTD (expression, dates, [filter])

Evaluates the value of the expression for the quarter


TOTALYTD (expression, dates, [filter],

Evaluates the value of thesub> expression for the year to

Function 
Desctiption 

ADDMISSINGITEMS(<showAllColumn>[,
[ROLLUPISSUBTOTAL(]<groupingColumn>[,<isSubtotal_columnName>][,<groupingColumn>][,<isSubtotal_columnName>]…[)], [, filterTable] …)

New in Excel 2016/Power BI Desktop.
Adds combinations of items from multiple columns to a table if they do


ALL (TableOrColumn, [TableOrColumn], …) 
Returns the table or column with filters removed. 

ALLEXCEPT (table, column, [column], …) 
Returns the table with all filters removed except for the filters on the


ALLNOBLANKROW (table  column) 
Returns a table, when the passed parameter was a table, or a column of


ALLSELECTED ([tableName  columnName]) 
Keeps filters on Rows and Columns in a pivot table while keeping the


CALCULATE (expression, [filter1], [filter2], …) 
Modifies the filter context prior to calculating the expression. 

CALCULATETABLE (expression, filter1, filter2,…) 
Modifies the filter context prior to returning a table of values. 

CROSSFILTER (columnName1, columnName2, direction)

Allows you to change the filtering direction for a single measure.


DISTINCT (column) 
Returns a 1 column table of all the distinct values in the current


EARLIER (column, [number]) 
Used to access a previous row context when more than 1 row context


EARLIEST (column) 
As above, but returns the absolute first row context. 

FILTER (table, filter) 
Returns a table containing only the filtered rows. 

FILTERS (columnName) 
Returns a table containing the list of values that are directly


HASONEFILTER (columnName)

Used to check if there is one and only one filter on a column in the


HASONEVALUE (columnName)

Used to check if there is one and only one value visible in a column in


SELECTEDVALUE(ColumnName) 
This is new in Power BI Desktop.


ISCROSSFILTERED (columnName)

Used to check if there is an indirect filter on a column in the current


ISFILTERED (columnName)

Used to check if there the column is filtered at all in the current


KEEPFILTERS (expression)

CALCULATE will replace a filter on the same column.


RELATED (column) 
Forces a row context to follow the relationship to a related table and


RELATEDTABLE (tableName) 
Forces a row context to follow the relationship to a related table and


SUBSTITUTEWITHINDEX (table, indexColumnName, indexColumnsTable,orderBy_expression, [order],

New in Excel 2016/Power BI DesktopReturns a table which represents a left semijoin of the two tables


The index starts at 0 (0based) and is incremented by one for each


USERELATIONSHIP (columnName1,

You can have more than 1 relationship between 2 tables in DAX, but only


VALUES (TableNameOrColumnName)

Returns a table consisting of a single column of unique values in the


VALUES (TableNameOrColumnName)

Returns a table consisting of a single column of unique values in the

Function 
Description 

CONTAINS (table, columnName, value,[columnName, value] …) 
Returns TRUE if each_{}specified value is contained in the corresponding


CUSTOMDATA () 
Returns the content of the_{}CustomData property in the connection string.Returns Blank, if_{}CustomData property was not defined at connection time. 

ISBLANK (value) 
Returns True if the value is blank.Otherwise, returns FALSE. 

ISEMPTY (table_expression) 

New in Excel 2016/Power BI Desktop.Returns True if the table is empty (has no rows),

ISERROR (value) 
Returns True if the value is an Error.Otherwise, returns FALSE. 

ISEVEN (number) 
Returns TRUE if number is even, Returns FALSE if number is odd.If number is nonnumeric, ISEVEN returns the #VALUE! error value.


ISLOGICAL (value) 
Returns TRUE if the value is a logical value (TRUE OR FALSE).Otherwise, returns FALSE. 

ISNONTEXT (value) 
Returns TRUE if the value is not text or blank Returns FALSE if the


ISNUMBER (value) 
Returns TRUE if the value is numeric.Otherwise, returns FALSE. 

ISODD (number) 
Returns TRUE if number is odd.Returns FALSE if number is even.If number is nonnumeric, ISODD returns the #VALUE! error value.


ISONORAFTER (scalar expression, scalar expression, [sort order], [scalar

New in Excel 2016/Power BI Desktop.This function takes a variable number of triples, the first two


and the third is the sort order – ascending (default) or descending.
Based on the sort order, the first parameter is compared with the second


ISTEXT (value) 
Returns TRUE if the value is text Otherwise, returns FALSE.Empty string is text.Blank is not text. 

LOOKUPVALUE ( result_columnName, search_columnName, search_value,[search_columnName, search_value],…) 
The value of result_column at the row where all pairs of search_column


USERNAME () 
Returns the username from the credentials given to the system at


Function 
Description 
AND (logical_value, logical_value) 
Checks whether both arguments are TRUE, and returns TRUE if both

FALSE () 
Returns the logical value FALSE. 
IF

Checks if a condition provided as the first argument is met. Returns one

IFERROR (value, value_if_error) 
Evaluates an expression and returns a specified value if the expression

NOT (logical_value) 
Changes FALSE to TRUE, or TRUE to FALSE. 
OR (logical_value, logical_value) 
Checks whether one of the arguments is TRUE to return TRUE. The function

SWITCH (Expression, value1, expression1,

Expression is evaluated and the result is matched with the given values.

TRUE () 
Returns the logical value TRUE. 

Description 

ABS (number)

Removes the negative sign if


ACOS (number) 
New in Excel 2016/Power BI Desktop.Returns the arccosine, or inverse cosine, of a number. The arccosine is


ACOSH (number) 
New in Excel 2016/Power BI Desktop.Returns the inverse hyperbolic cosine of a number. The number must be


ASIN (number) 
New in Excel 2016/Power BI Desktop.Returns the arcsine, or inverse sine, of a number. The arcsine is the


ASINH (number)

New in Excel 2016/Power BI Desktop.Returns the inverse hyperbolic sine of a number. The inverse hyperbolic


ATAN (number) 
New in Excel 2016/Power BI Desktop.Returns the arctangent, or inverse tangent, of a number.The arctangent is the angle whose tangent is_{ number}.


ATANH (number)

New in Excel 2016/Power BI Desktop.Returns the inverse


CEILING (number, significance) 
Rounds a number up, to the nearest integer or to the nearest


COMBIN (number, number_chosen) 
New in Excel 2016/Power BI Desktop.Returns the number of


COMBINA (number, number_chosen)

New in Excel 2016/Power BI Desktop.Returns the number of


COS (number)

New in Excel 2016/Power BI Desktop.Returns the cosine of the given angle. 

COSH (number)

New in Excel 2016/Power BI Desktop.Returns the hyperbolic cosine of a number. 

CURRENCY (value)

The


DEGREES (angle)

New in Excel 2016/Power BI Desktop.Converts radians into degrees. 


denominator, 
Safe divide function

EVEN (number)

New in Excel 2016/Power BI Desktop.Returns number rounded up to the nearest


EXP (number)

New in Excel 2016/Power BI Desktop.Returns e raised to the power of a given number. The constant e equals


FACT (number)

Returns the factorial of a number, equal to the series 1*2*3*…* ,


FLOOR (number, significance)

Rounds a number down,


GCD (number1, [number2], …)

New in Excel 2016/Power BI Desktop.Returns the greatest common divisor of two or more integers. The


INT (number)

Rounds a number down to the


ISO.CEILING (number, [significance])

New in Excel 2016/Power BI Desktop.Rounds number up, to the nearest multiple of significance.Rounds number up, to the


LCM (number1, [number2], …) 
New in Excel 2016/Power BI Desktop.Returns the least common multiple of integers. The least common multiple


LN (number)

Natural Log 

LOG (number, base) 
You might receive an error


LOG10 (number) 
Returns the base10


MOD (number, divisor) 
If the divisor is 0 (zero), MOD returns an error. You cannot


MROUND (number, multiple)

New in Excel 2016/Power BI Desktop.Returns a number rounded to


ODD (number)

New in Excel 2016/Power BI Desktop.Returns number rounded up to


PERMUT (number, number_chosen)_{} 
Returns the number of


PI () 
New in Excel 2016/Power BI


POWER (number, power) 
Returns the result of a number raised to a power. 

QUOTIENT (numerator, denominator) 
New in Excel 2016/Power BI Desktop.Performs division and


RADIANS (angle) 
New in Excel 2016/Power BI


RAND () 
Returns a random number


RANDBETWEEN (bottom, top)

Returns a random number in the range between two numbers you specify. 

ROUND (number, num_digits) 
Rounds a number to the specified number of digits. 

ROUNDDOWN (number, num_digits)

Rounds a number down, toward


ROUNDUP (number, num_digits) 
Rounds a number up, away


SIGN (number) 
Determines the sign of a


SIN (number) 
New in Excel 2016/Power BI Desktop.Returns the sine of the given angle. 

SINH (number) 
New in Excel 2016/Power BI Desktop.Returns the hyperbolic sine


SQRT (number)

Returns the square root of a number.If the number is negative,


SQRTPI(number) _{ } 
New in Excel 2016/Power BI Desktop.Returns the square root of (number * pi). 

TAN (number) 
New in Excel 2016/Power BI Desktop.Returns the tangent of the


TANH (number) 
New in Excel 2016/Power BI Desktop.Returns the hyperbolic


TRUNC (number, num_digits) 
Truncates a number to an


Description 
DATATABLE

New in Excel 2016/Power BI DesktopReturns a table declaring an inline set of values.Each of the columns is

EXCEPT

Returns a table that contains the rows of one table minus

GROUPBY

New in Excel 2016/Power BI

INTERSECT

New in Excel 2016/Power BI Desktop.Returns the row intersection

NATURALINNERJOIN

New in Excel 2016/Power BI Desktop.Performs an inner join of a table with another table. The tables are

NATURALLEFTOUTERJOIN(leftJoinTable, rightJoinTable)

New in Excel 2016/Power BI Desktop.Performs an inner

UNION (table_expression1, table_expression2)

New in Excel 2016/Power BI Desktop.Creates a union (join) table from a pair of tables.Returns a table that

GENERATE (table1, table2) 
Returns a table with the Cartesian product between each row in table1

GENERATEALL (table1, table2) 
Returns a table with the Cartesian product between each row in table1

GENERATESERIES (StartValue,EndValue, [IncrementValue]) 
Generates a table of values using the parameters provided 
VAR VarName = Expression

New in Excel 2016/Power BI Desktop.Stores the result of

AVERAGEX (table, expression)_{} 
Calculates the average

CONCATENATEX (table, expression,[delimiter])

New in Excel 2016/Power BI Desktop.Concatenates the result of an expression evaluated for each

COUNTAX (table, expression)

The COUNTAX function

COUNTX (table, expression) 
Counts the number of rows that contain a number or an expression that

GEOMEANX (table, expression)_{} 
New in Excel 2016/Power BI Desktop.Returns the geometric mean of an expression evaluated for each row in a

MAXX (table, expression) 
Evaluates an expression for each row of a table and returns

MEDIANX (table, expression) 
New in Excel 2016/Power BI Desktop. 
Returns the median of an


MINX (table,

Returns the smallest numeric

PERCENTILEX.EXC

New in Excel 2016/Power BI Desktop.Returns the percentile number of an expression evaluated for

PERCENTILEX.INC

New in Excel 2016/Power BI Desktop.Returns the percentile number of an expression evaluated for

PRODUCTX (table, expression) 
New in Excel 2016/Power BI Desktop.Returns the product of an expression evaluated for each row

RANKX (table, expression, [value],[order], [ties])_{} 
Returns the ranking of a number in a list of numbers for

STDEVX.P (table, expression)_{} 
expression is any DAX expression that returns a single

SUMX (table, expression)_{} 
Returns the sum of an expression evaluated for each row in a

VARX.P (table, expression) 
Returns the variance of the entire population. 
VARX.S (table, expression)_{} 
Returns the variance of a sample population. 


XIRR (table, values, dates, [guess])_{} 
New in Excel 2016/Power BI Desktop.Returns the internal rate of

XNPV (table, values, dates, rate)

New in Excel 2016/Power BI Desktop.Returns the present value

IGNORE (expression) 
IGNORE function does not return a value.IGNORE can be used as an expression argument to

ROLLUPADDISSUBTOTAL ( groupBy_columnName,

ROLLUPADDISSUBTOTAL function does not

ROLLUPGROUP

ROLLUPGROUP

Function 
Description 
BLANK ()_{} 
Returns a blank.Blanks are not equivalent to nulls. DAX uses blanks for both database

CODE (text)_{} 
Returns a numeric code for the first character in a text string. The

CONCATENATE (text1, text2)

Joins two text strings into one text string.If you need to add more than two arguments, use the AND (&) operator. 
EXACT (text1, text2) 
Compares two text strings and returns TRUE if they are exactly the same,

FIND

Returns the starting position of one text string within another text

FIXED

Rounds a number to the specified number of decimals and returns the

FORMAT (value, format_string) 
Converts a value to text according to the specified format. 
LEFT (text, num_chars) 
Returns the specified number of characters from the start of a text

LEN (text) 
Returns the number of characters in a text string. 
LOWER (text) 
Converts all letters in a text string to lowercase. 
MID (text, start_num, num_chars)

Returns a string of characters from the middle of a text string, given a

REPLACE

REPLACE replaces part of a text string, based on the number of

REPT (text, num_times) 
Repeats text a given number of times. Use REPT to fill a cell with a

RIGHT (text, num_chars) 
RIGHT returns the last character or characters in a text string, based

SEARCH (find_text, within_text, [start_num], [NotFoundValue])_{} 
Returns the number of the character at which a specific character or

SUBSTITUTE (text, old_text, new_text, instance_num) 
Replaces existing text with new text in a text string. 
TRIM (text)_{} 
Removes all spaces from text except for single spaces between words. 
UPPER (text)_{} 
Converts a text string to all uppercase letters 
VALUE (text)_{} 
You need not use the VALUE function in a formula because Power Pivot

Function 
Description 
PATH (ID_columnName,

Returns a delimited text string with the identifiers of all the parents

PATHCONTAINS (path, item) 
Returns

PATHITEM (path, position, [type]) 
Returns the item at the specified_{position }from a

PATHITEMREVERSE (path, position,[type]) 
Returns the item at the specified_{position }from a

PATHLENGTH (path) 
Returns the number of parents to the specified item in a given PATH

Function 
Description 

BETA.DIST

New in Excel 2016/Power BI Desktop.Returns the beta distribution. The beta distribution is commonly used to


BETA.INV (probability, [A], [B]_{}alpha, beta, 
New in Excel 2016/Power BI Desktop.Returns the inverse of the beta cumulative probability density function


CHISQ.INV deg_freedom)_{} 
(probability, 
New in Excel 2016/Power BI Desktop.Returns the inverse of the lefttailed probability of the chisquared

CHISQ.INV.RT deg_freedom)_{} 
(probability, 
New in Excel 2016/Power BI Desktop.Returns the inverse of the righttailed probability of the chisquared

CONFIDENCE.NORMstandard_dev, size)_{} 
(alpha, 
New in Excel 2016/Power BI Desktop.The confidence interval is a range of values. Your sample mean, x, is at

CONFIDENCE.Tstandard_dev, size)_{} 
(alpha, 
New in Excel 2016/Power BI Desktop.Returns the confidence interval for a population mean, using a Student’s

EXPON.DIST

lambda, 
New in Excel 2016/Power BI Desktop.Returns the exponential distribution. Use EXPON.DIST to model the time

GEOMEAN (column)_{} 
New in Excel 2016/Power BI Desktop.Returns the geometric mean of the numbers in a column. 

MEDIAN (column) _{ } 
New in Excel 2016/Power BI Desktop.Returns the median of numbers in a column. 

PERCENTILE.EXC (column, k)_{} 
New in Excel 2016/Power BI Desktop.Returns the kth percentile of values in a range, where k is in the


PERCENTILE.INC (column, k)_{} 
New in Excel 2016/Power BI Desktop.Returns the kth percentile of values in a range, where k is in the


POISSON.DIST

Returns the Poisson distribution.
A common application of the Poisson distribution is predicting the


RANK.EQ

Returns the ranking of a number in a list of numbers. 

SAMPLE (n_value, table,[orderBy_expression], [order],
[orderBy_expression], [order],
…)

Returns a sample of N rows from the specified table. 

STDEV.P (ColumnName) _{ } 
Returns the standard deviation of the entire population. 

STDEV.S (ColumnName)_{} 
Returns the standard deviation of a sample population. 

VAR.P (columnName) 
Returns the variance of the entire population. 

VAR.S (columnName)_{} 
Returns the variance of a sample population. 
Function 
Description 
AVERAGE (column)

Returns the average (arithmetic mean) of all the numbers in

AVERAGEA (column)

The AVERAGEA

COUNT (column) 
Counts numbers only in the

COUNTA (column) 
Counts text values as well

COUNTBLANK (column) 
Counts the number of blank

COUNTROWS (table) 
The COUNTROWS function counts the number of rows in the specified table,

DISTINCTCOUNT (column) 
Counts each value in a column once and only once

MAX (column) 
Returns the largest numeric

MAXA (column) 
Like MAX, however also considers Dates and Logical values, such as TRUE

MIN (column) 
Returns the smallest numeric

MINA (column) 
Like MIN, however also

PRODUCT (column) 
New in Excel 2016/Power BI Desktop.
Multiplies all the values in a column together.

SUM (column) 
Adds all the numbers in a

TOPN (n_value, table, orderBy_expression, [order], [orderBy_expression,

Returns a table containing the top N rows.
