Functions

If you've chosen EU number format, all commas should be replaced by semicolon.

Example:

• US format: sum_last("Revenue", 12)

• EU format: sum_last("Revenue"; 12)

avg("row_name"[range_start : range_end])

The avg() function calculates the average of the values in the specified time series from range_start to range_end.

Example: avg("Revenue"[-3:0])

avg_ytd("row_name")

The avg_ytd() function returns the average of the values in a time series from the beginning of the fiscal year until and including the current month.

Example: avg_ytd("Revenue")

avg_last_year("row_name")

The avg_last_year() function returns the average of the values in the previous fiscal year. This function is influenced by your fiscal year settings.

Example: avg_last_year("Revenue")

avg_last("row_name", num_months)

The avg_last() function returns the average of the values in the time series going back X months, as defined by the num_months parameter.

Example: avg_last("Revenue", 6)

median("row_name"[range_start : range_end])

The median() function returns the median of the values in the specified time series from range_start to range_end

Example: median("Revenue"[-3:0])

median_ytd("row_name")

The median_ytd() function returns the median of the values in a time series from the beginning of the fiscal year until and including the current month.

Example: median_ytd("Revenue")

median_last_year("row_name")

The median_last_year() function returns the median of the values in the previous fiscal year. This function is influenced by your fiscal year settings.

Example: median_last_year("Revenue")

median_last("row_name", num_months)

The median_last() function returns the median of the values in the time series going back X months, as defined by the num_months parameter.

Example: median_last("Revenue", 6)

sum("row_name"[range_start : range_end])

The sum() function calculates the total of the values in the specified time series from range_start to range_end.

Example: sum("Revenue"[-3:0])

sum_ytd("row_name")

The sum_ytd() function returns the sum of the values in a time series from the beginning of the fiscal year until and including the current month.

Example: sum_ytd("Revenue")

sum_last_year("row_name")

The sum_last_year() function returns the sum of the values in the previous fiscal year. This function is influenced by your fiscal year settings.

Example: sum_last_year("Revenue")

sum_last("row_name", num_months)

The sum_last() function returns the sum of the values in the time series going back X months, as defined by the num_months parameter.

Example: sum_last("Revenue", 6)

receivables("row name", [payment days])

The receivables() function accumulates revenue amounts from previous months based on an assumption of payment days. Based on the number of payment days, the function loops back through previous months to collect revenue as part of receivables.

The function assumes 30 days per month. The payment days input must be hardcoded and cannot reference a cell.

Example: receivables("revenue", 30)

ignore_div_zero(a/b)

Theignore_div_zero() returns a zero if the function's output is a #DIV!/0 error. The function is used for margin and percentage calculations, where the denominator is occasionally zero.

power(num, exponent)

The power() function is a mathematical function that returns the value of the parameter num to the power of the exponent parameter.

Example: power(10, 2) returns 100

min(a,b)

The min() function returns the smallest numeric value of the input values.

max(a,b)

The max() function returns the largest numeric value of the input values.

abs(num)

The abs() function returns the absolute value of a number.

round(num, [decimals])

The round() function rounds the number value up or down to the nearest integer. Optionally, provide desired number of decimals.

round_down(num)

The rounddown() function rounds numbers down to the nearest integer.

round_up(num)

The roundup() function rounds numbers up to the nearest integer.

if(condition, value_if_true, value_if_false)

The if() function allows you to create a conditional calculation, similar to what is available in Excel or Google Sheets.

if(condition, value_if_true, value_if_false)

Example to calculate gross profit margin:

if("Revenue"[0]=0,0,"Gross profit"[0]/"Revenue"[0])

If revenue is zero, display zero; otherwise, calculate the gross profit margin. This specific example prevents #ERRs from dividing by zero.

if_month(a, b)

The if_month function returns 1 if the time period is equal to the month number input in the formula, else 0.

• if_month(1) will return 1 if the month is January, else 0

• if_month(1, 6) will return 1 if the month is January or June, else 0

if_quarter_month(num)

The if_quarter_month function returns 1 if the time period is equal to the month number inside a given quarter. E.g., you can input [1;3] into the formula.

• if_quarter_month(1) returns 1 if the month is Jan, Apr, Jul, or Oct

• if_quarter_month(2) returns 1 if the month is Feb, May, Aug, or Nov

• if_quarter_month(3) returns 1 if the month is Mar, Jun, Sep, or Dec