Function reference
Every function, aggregation and window calculation available in calculated columns and measures. Generated from the formula engine, so a signature here is the signature the engine implements.
70 of 70 shown
Functions
Called inside an expression.
TOTEXT(value) → textTo textConverts any value to text.TONUMBER(value) → decimalTo numberConverts a value to a number. Empty when it cannot be converted.TOINTEGER(value) → whole numberTo whole numberConverts a value to a whole number. Empty when it cannot be converted.TODATE(value) → dateTo dateConverts a value to a date. Empty when it cannot be converted.YEAR(date) → whole numberYearThe year of a date, as a number.MONTH(date) → whole numberMonthThe month of a date (1–12).DAY(date) → whole numberDayThe day of the month (1–31).DATEDIFF(unit, from, to) → whole numberDate differenceThe number of whole units between two dates.DATEADD(unit, date, amount) → same type as dateDate addShifts a date by a number of units (use a negative amount to go back).TODAY() → dateTodayToday's date.NOW() → date & timeNowThe current date and time.AND(condition…) → true/falseANDTrue when every condition is true.OR(condition…) → true/falseORTrue when at least one condition is true.NOT(condition) → true/falseNOTFlips true to false and false to true.EQUALS(a, b) → true/falseEquals (=)True when both values are the same.NOTEQUALS(a, b) → true/falseNot equal (≠)True when the values differ.GREATERTHAN(a, b) → true/falseGreater than (>)True when the first value is larger.GREATEROREQUAL(a, b) → true/falseGreater or equal (≥)True when the first value is larger or the same.LESSTHAN(a, b) → true/falseLess than (<)True when the first value is smaller.LESSOREQUAL(a, b) → true/falseLess or equal (≤)True when the first value is smaller or the same.COALESCE(value…) → same type as valueFirst non-emptyThe first value that is not empty.NULLIF(value, compareTo) → same type as valueEmpty if equalReturns empty when both values are equal, otherwise the first value.ISNULL(value) → true/falseIs emptyTrue when the value is empty.ISNOTNULL(value) → true/falseIs not emptyTrue when the value is not empty.ADD(a, b) → numberAdd (+)Adds two numbers.SUBTRACT(a, b) → numberSubtract (−)Subtracts the second number from the first.MULTIPLY(a, b) → numberMultiply (×)Multiplies two numbers.DIVIDE(a, b) → decimalDivide (÷)Divides the first number by the second. Returns empty when the divisor is 0.NEGATE(value) → same type as valueNegateFlips the sign of a number.MOD(a, b) → numberModuloThe remainder after dividing the first number by the second.ROUND(value, [decimals]) → decimalRoundRounds a number to the given number of decimal places.FLOOR(value) → whole numberRound downRounds a number down to the nearest whole number.CEILING(value) → whole numberRound upRounds a number up to the nearest whole number.ABS(value) → same type as valueAbsolute valueThe number without its sign.POWER(base, exponent) → decimalPowerRaises a number to the given exponent.SQRT(value) → decimalSquare rootThe square root of a number.CONCAT(text…) → textCombine textJoins several text values into one. Empty values are skipped.UPPER(text) → textUPPERCASEConverts text to uppercase.LOWER(text) → textlowercaseConverts text to lowercase.TRIM(text) → textTrim spacesRemoves spaces from the start and end of text.LENGTH(text) → whole numberText lengthThe number of characters in a text value.SUBSTRING(text, start, [length]) → textSubstringExtracts part of a text value, starting at a position (1 = first character).REPLACE(text, find, replaceWith) → textReplaceReplaces every occurrence of a search text with a replacement.LEFT(text, count) → textLeftThe first N characters of a text value.RIGHT(text, count) → textRightThe last N characters of a text value.CONTAINS(text, search) → true/falseContainsTrue when the text contains the search text.Aggregations
Summarise a column across rows.
SUM(column) → same type as columnSumAdds up every value.AVG(column) → decimalAverageThe mean of the values.MIN(column) → same type as columnMinimumThe smallest value.MAX(column) → same type as columnMaximumThe largest value.COUNT(column) → whole numberCountHow many non-empty values there are.COUNT_DISTINCT(column) → whole numberCount (Distinct)How many different values there are.FIRST(column) → same type as columnFirstThe first value encountered.LAST(column) → same type as columnLastThe last value encountered.MEDIAN(column) → decimalMedianThe middle value.STDDEV(column) → decimalStandard deviationHow spread out the values are.P25(column) → decimal25th percentileA quarter of the values are below this.P75(column) → decimal75th percentileThree quarters of the values are below this.P90(column) → decimal90th percentileNine tenths of the values are below this — useful for spotting outliers.Window calculations
Computed along a chart’s axis; needs an ordered axis.
Running total — applied to a measure along the chart's axisRunning totalA cumulative sum along the axis (each point adds all previous points).% of visible total — applied to a measure along the chart's axis% of visible totalEach point as a share of what the chart currently shows (or of its series). Filters change the total.Difference from previous — applied to a measure along the chart's axisDifference from previousHow much each point changed from the point before it.% change from previous — applied to a measure along the chart's axis% change from previousThe percentage change from the previous point — year-over-year on a yearly axis, month-over-month on a monthly one.Moving average — applied to a measure along the chart's axisMoving averageThe average over the last N points, smoothing out short-term noise.Rank — applied to a measure along the chart's axisRankThe position of each point when sorted by value.Year to date — applied to a measure along the chart's axisYear to dateAdds up each point and everything before it in the same year, restarting each January.Quarter to date — applied to a measure along the chart's axisQuarter to dateAdds up each point and everything before it in the same quarter.Month to date — applied to a measure along the chart's axisMonth to dateAdds up each point and everything before it in the same month.Same period last year — applied to a measure along the chart's axisSame period last yearThe value from the matching period one year earlier — blank in the first year, where there is nothing to compare to.% change vs last year — applied to a measure along the chart's axis% change vs last yearHow much each point changed against the matching period one year earlier.